SQL Exploits

by AOnRkjk=

In the letters section of 21:4, Citron mentions an SQL exploit.  I thought an article providing some further explanation might be appropriate since I haven't seen one in 2600 yet.

Structured Query Language (SQL) provides a standardized syntax for querying databases.

It is implemented in databases from various vendors and is parsed by the vendor-supplied database drivers.  The syntax includes the ability to supply variables, referred to as "host variables."  If you've ever seen question marks ("?") in an SQL statement or a call to a stored procedure, that is one of the ways to provide placeholders for the variables.

Now let's say you need to allow users to log into a web site with a username and password.  The program needs to obtain these variables from a web form, store them as strings, then query the database and return a user ID or a "not found" condition.

The values of the program variables need to be passed to the database.  Therefore, the parameter list in a call to the database driver includes both the SQL statement to be executed and an array containing the values of the variables.

When the SQL statements are embedded in a program, all this happens pretty much automatically.

For example:

#sql [connCtx] {
  select userid into :userid from users where username = :username and password = :password
}

By coding it in this manner, the SQL statement will be parsed as it was intended by the developer.

Whether this SQL statement is parsed at compile time or run time, any data in the program's username and password variables will be compared to the values in the database.  If there are any special characters or other invalid data in these fields, it is likely that those values will not exist and the database will return a "not found" condition.

So if the developer has this much control over how an SQL statement is parsed, where's the weakness?  Let me give you an example from personal experience.

One night I got a call from a coworker who was on his way into work and wanted some assistance.  He had been called in to restore a database because it had been discovered that all of the rows in the table had been updated with bad data.

This should not occur, since programs should only be updating a few rows of this table at a time.  My guess was that this had probably been caused by a single SQL UPDATE statement, and so I suggested that before doing anything else we should bring up the database monitor and check the page that shows the SQL statements that have used the most system resources.

This might allow us to identify the errant SQL and determine why this happened in the first place.  As it turned out, it allowed us to run another update to reverse the errant one and avoid doing a restore (and losing all of the other updates done earlier that day).

In this case, the intent of the update was to change some numeric values in a specific row.  In the past, we might have coded the UPDATE statement like this (this is a simplification, showing only two fields being updated):

#sql [connCtx] {
  update tb1 set amt1 = amt1 - :val1, amt2 = amt2 - :val2 where rowid = :rowid
}

However, our company started switching to "dot NET" a couple of years ago, and this application had been developed in this new environment.

In this environment, code equivalent to the UPDATE statement above might be:

cmd = db.CreateCommand (
  "update tb1 set amt1 = amt1 - @val1, amt2 = amt2 - @val2 where rowid = @rowid"
);
cmd.Parameters.Add(New SqlParameter("@val1", SqlDbType.SmallMoney);
cmd.Parameters("@val1").Value = val1;
cmd.Parameters.Add(New SqlParameter("@val2", SqlDbType.SmallMoney);
cmd.Parameters("@val2").Value = val2;
cmd.Parameters.Add(New SqlParameter("@rowid", SqlDbType.VarChar);
cmd.Parameters("@rowid").Value = rowid;
db.ExecuteNonQuery(cmd);

As you can see, the code is now a bit more cumbersome, especially if there are a lot of columns to be updated.

As a result, a developer may be inclined to take a shortcut and, taking advantage of the string concatenation operator, code it this way instead:

cmd = db.CreateCommand (
  "update tb1 set amt1=amt1-" & val1 & ",amt2=amt2-" & val2 & " where rowid='" & rowid & "'"
);
db.ExecuteNonQuery(cmd);

So let's examine what happens when a user enters a positive amount (123) for val1 and a negative amount (-456) for val2 in an attempt to update a single row: rowid='789'

After the concatenation operations, the SQL passed to CreateCommand() will look like this:

update tb1 set amt1=amt1-123,amt2=amt2--456 where rowid='789'

In SQL, comments begin with two consecutive hyphens: --

Since comments can be ignored, the UPDATE statement above is equivalent to:

update tb1 set amt1=amt1-123,amt2=amt2

Without a WHERE clause, the result of the UPDATE statement is to subtract 123 from every amt1 in the entire table (as well as replace every amt2 with the same value).

These particular input values have caused the SQL statement to be parsed and executed in a completely different way than what was intended by the developer!

To provide a similar example for an SQL statement that uses strings rather than numbers, let's revisit the exploit mentioned by Citron.

Let's say the SQL statement is constructed like this:

select userid from users where (username = '" & username & "') and (password = '" password & "')

Now if for both the username and password fields, you enter this:

' or '' = '

The resulting SQL statement becomes:

select userid from users where (username = '' or '' = '') and (password = '' or '' = '')

Executing this SELECT statement will return all of the rows in the users table.

Therefore this form of the exploit may take a long time to execute and will work only if the SELECT statement does not time out and is followed by code that retrieves the first row returned and discards the rest.

If the SELECT statement had instead been coded as a single-row SELECT INTO, the database would have simply returned an error.

In this case, the input would need to be constructed more carefully, so that the userid for only one user was returned.
Return to $2600 Index