Safe SQL
(vulnerability testing)

Russell Bateman
October 2011
last update:

How to Test Structured Query Language (SQL) Vulnerability

As a developer one is expected to unit-test written code. Included in testing Web-facing applications, the vulnerability of SQL queries and updates is a concern. Passing some of what's below help in this process. Add the lines to an input field that will be injected into a SQL statement.

A most common case

A simple thing to do is attempt to inject a single or double-quote into the application, and thence into the SQL query. Let's say you entered the following sequence in response to a Name field. 

    ' OR 1=1--

What this accomplishes is clearer when you consider the underlying SQL: 

    SELECT * FROM users WHERE name = '

...to which, after the name is gathered from the user's resonse, is concatenated the name and a closing single-quote: 

    SELECT * FROM users WHERE name = '' OR '1'='1' --';

What was supposed to happen was: 

    SELECT * FROM users WHERE name = 'jack';

...but what really happened, see the statement above with the OR in it, is...

"look in the users table for a null name (would fail), but also accept the condition 1 == 1, which will evaluate to true, and handle the unmatched single-quote concatenated by the application to close the name (which would produce an error) by commenting it out via --."

Of course, a second form is also added to make up for the application using double-quote: 

    " OR 1=1--

...and other, possible commenting syntax in place of -- can be attempted to when the identity of the backing database is unknown including ({, /*, etc.

So, vulnerable or not?

If you inject a assortment of the above tricks and get error messages, you learn two things:

  1. Your SQL may not be vulnerable (since it errors out), but...
    2.  
  2. Your SQL error may instead give back information you did not wish to make public. The solution to this is to filter error messages, funnel them to your own, to more generic messages less revealing of database assumptions including column and table names, etc.

Anti-tricks

One way of thwarting this sort of thing lies in making use of "prepared statements" in your code. In Java, this is using PreparedStatement or similar classes to build your query and update statements. This works because no user input is embedded directly into the SQL statement; instead, it's built using scalar parameters, with user input being bound to one or more parameters.

Another way around this is to make use of object-relational mapping (ORM) frameworks such as Hibernate which take SQL statement writing out of your hands and employ prepared (or parameterized) statements from object-oriented code.