SQL INJECTION TESTING -- PART I (a partial list)

There is a table named "login2" that contains fields:"userid"; "password"; and other secret data. The intent is gain access to the system. Here is the contents of a simple login table (called "login2")

1. The simple form below (method="POST") is preloaded with a valid userid and password. Click the button and watch it log you in
   (NO client side edits -- no nothing. A naked submission)

   Userid
   Password
   

2. A Simple SQL Injection

   The idea is to create a syntactically valid SQL statement that is ALSO always true.

   We use:

   • some data followed by a single quote
   • The OR operator
   • and an ALWAYS TRUE statement (like 1=1)
   • then the "--" SQL operator that TERMINATES the rest of the SQL query
   • so, anything you enter beyond the "--" is ignored

   Userid
   Password
   

   WE are IN!!! We get the third record since we did not specify an "ORDER BY" clause, SQL server uses the primary key (i.e., user_id) as the default "ORDER BY" sequence. So "carly" (the third row shown above) is at the top. The intruder has thus SUCCESSFULLY gotten access to the data. Whew!

3. To prevent this SQL injection from working, we CAN add an edit that removes all single quotes from the data fields. Thus, we eliminate the possible use of the "'anything OR 1=1" injection.

   e.g., change:
   "SELECT * FROM login WHERE user_id="+chr(39)+Request.Form("uid")+chr(39)+...
   to
   "SELECT * FROM login WHERE user_id="+chr(39)+replace(Request.Form("uid"),"'","")+chr(39)+...

   Userid
   Password
   

   Works OK (i.e., Stops the SQL injection from working).

4. Then your boss (old man O'Conner) tries to login:

   Userid
   Password
   

   Doesn't work for him. He cannot login. He is happy with his quote, he is gonna make you UNHAPPY and you might lose your job. So...

5. Instead of disallowing any single quotes, we "escape" the single quotes by replacing every single quote with TWO single quotes. SQL will recognize this doubled single quote as an attempt to insert a single quote and it will work.

   Userid
   Password
   

   Yea! -- the boss is happy and ...

6. Again attempting to perform the SQL Injection...

   Userid
   Password
   

   Yea! This prevents the SQL Injection from working. we are safe? Not yet. Suppose we are using...

7. A numeric instead of a character field

   Suppose that we are using a userid which is numeric. Consider this table "login3":

   Now the exploitation of the single quote to terminate the userid field and allow us to insert " OR 1=1--" DOESN'T WORK. Try this:

   Userid
   Password
   

   This fails because the userid field is not a number and we have used added a single quote to the this field. Clearly a type error. But...

8. This version that uses a number without quotes DOES work -- because the SQL statement that would be used when the userid field is numeric doesn't contain quotes anyway. Note the change in the first field where any number can be used with the " OR 1=1" injection.

   Userid
   Password
   

   Oops. The SQL injection now works because the userid field is now numeric and the " OR 1-1--" gets executed. Now what?

9. Now we have to something else. A little data scrubbing (sanitizing or editing) will stop the single quote version of the injection, a different strategy will need to be employed in the case where the target field is numeric. For example, we could edit the userid field and be sure it is a number. Like this:

   Userid
   Password
   

   This stops the injection.

Scrubbing (sanitizing)(editing) the data

We begin to get the idea that we REALLY need to edit the data (i.e., scrub or sanitize). This is easier said than done. While numerics are relatively easy to edit, character fields such as email addresses often defy validation. Further, the ability to use alernative escape sequences, hexadecimal and various other encoding schemes make perfect validation nearly impossible. There is the maxim that:

• Numeric Database Fields

  In accounting applications, we cannot get around needing the following characters:

  0 1 2 3 4 5 6 7 8 9 0 . - (a basic WHITELST)

  We need these AND NO MORE! Commas are NOT needed; NOR $ (the dreaded currency data type in SQL should be avoided); NOR left or right parenthesis (which by the way WILL work in SQL); and we will only allow ONE minus (Note that the injection uses the "--" string to terminate the remainder of the SQL statement)

• Character Data Fields

  There are some obvious characters to disallow (a basic BLACK list):

  & > < / \ ; % = " # ( ) @ and the ' (for the single quote we escape it by replacing it with two single quotes)

Parameterize the SQL statements

Rather than taking the raw user input as provided, we use the "command" object. Here is a typical ASP/SQL snippet with two user inputs (key1 is a numeric and key2 is a character field):

set cn=Server.CreateObject("ADODB.Connection")
cn.open "database_name","userid","password"
sql_str="SELECT * from some_table WHERE key1="+cstr(Request.Form("user_key1"))+" AND key2="+chr(39)+
Request.Form(user_key2")+chr(39)
set rs=cn.execute (sql_str)

Instead we do this:

set cn=Server.CreateObject("ADODB.Connection")
cn.open "database_name","userid","password"
set cm=Server.CreateObject("ADODB.Command")
set cm.ActiveConnection = cn
cm.CommandText="SELECT * from some_table WHERE key1=? AND key2= ?"
cm.parameters.refresh
cm(0)=Request.Form("user_key1")
cm(1)=Request.Form("user_key2")
set rs=cm.execute

The SQL engine indpendently evaluates the validity of the parameters cm(0) and cm(1) (i.e., the values the user provided through the form variables) by using what it knows about the database columns stated in the SQL statement -- it DOES NOT execute the contents of the parameter as it would if it were just part of the entire SQL string. For example, if cm(0) is an integer field (as defined in the CREATE statement), then the contents of cm(0) must be an ONLY an integer. Lets run the two SQL injections -- without editing the input data.

10. Parameterized version of the query (valid character data)

    The following worked on the "login2" (see example 1 above)

    Userid
    Password
    

    The valid user is able to login.

11. Parameterized version of the query (character data and an SQL injection in the user data)

    The following SQL injection worked on the "login2" (see example 2 above)

    Userid
    Password
    

    This injection fails to get a valid record because the injection was NOT executed as it would have been on a naive SQL string creation.

    It might also fail if the length of the userid (as defined in the database) is exceeded by the SQL injection string length. The login2 table defines userid as having 15 characters. The example below uses 18 characters.

    Userid
    Password
    

    Notice that the field length has been exceeded and we get an error message rather than a failed lookup.

12. Parameterized version of the query (valid integer data)

    Uses the login3 table where userid is an integer. This valid numeric userid allows the login:

    Userid
    Password
    

13. Parameterized version of the query (with SQL injection)

    Here we try the same injection as in step 8 above (the injection allowed us to login)

    Userid
    Password
    

    We get an error. Yea!!! This is because the userid field is not valid (it contains characters which are not numeric). The cm(0) parameter is not filled in and the SQL fails to be valid.

    Note: these "parameterization" examples prevented the injection WITHOUT edits. Some people claim this is adequate. This is just a claim. Hackers are smart enough to perhaps get around this method.

So...

• We sanitize on the server side (i.e., we edit the data: by type, by size, and by removing dangerous characters)
• and we parameterize.

See Part II here.