Stephen Smith's Blog

All things Sage 300…

Difficulties with SQL Statements

leave a comment »

In the good old days, database APIs separated the commands from the data, so usually you passed in a command like update or read to one parameter and the data to update or read in a separate parameter as a data buffer. In this case the data wouldn’t interfere with the commands or cause any sort of code execution.

Now in the brave new world (well not that new really), data is mixed with commands in the form of SQL statements that are passed to the database as a single string containing both the data and the command, like “SELECT * FROM ARCUS WHERE IDCUS = 1200 AND SWACT = 1”. Now if the data is manipulated by hackers they can insert additional commands to be executed.  For instance in the string before what if the use typed “1200 OR 1=1”, then it would return data for all customers. To avoid this you put the data in quotes like “IDCUS = ‘1200’. But then what if quotes are in the string? The solution is to “escape” them. For SQL this means doubling them up, ie changing ‘ to ”.

Theoretically this should make the SQL statement secure. However there are some subtle points, such as if the string is all single quotes then you need a buffer twice the size of the original to handle the data. What about leading and trailing spaces, how should these be handled? Is escaping single quotes sufficient? Are there other “special” characters that need to be dealt with?

Generally if you know your database you can handle this easily. However a gotcha can happen if you change databases. For instance the scheme above works fine for SQL Server and TSQL, but if you switch to MySQL, it becomes inadequate because \ is a valid escape character. So if the user enters \’, it gets changed to \” which now leaves one unquoted quote for hackers to maliciously use.

Besides user data causing invalid SQL statements, hackers can exploit these problems to find ways to execute extra code. If you can close the quote then the can add any SQL command they like, resulting in a SQL Injection attack.

Anyway it adds to the consequences of getting it wrong. Some databases provide routines to safely quote strings in their APIs. Its best to use these, since the database vendor should know all the ways to escape characters (documented or not) and hopefully these routines have been well tested (including by white hat hackers).

Written by smist08

March 7, 2009 at 4:49 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: