Preventing SQL injection (again)

Recently I had to update an old PERL program which, when it was originally written, had no sanitation of user input for SQL statements. The user input (from the web) was simply concatenated into SQL statements. This made it very vulnerable to SQL injection.

The SQL DBI used in the program did not allow parameterized queries and replacing it with a newer DBI would have required massive logic changes to the program. The solution was to figure out how to properly escape special characters present in the input. This turned out to be pretty simple if the input was surrounded by single quotes within the SQL statement. Assuming this is true, single quotes present in the input can be replace with with two single quotes. This will protect the SQL from injection.

Why? ANSI SQL says that a single quote is escaped by inserting an additional single quote directly before it. Escaping single quotes makes it very difficult if not impossible for the input to terminate the SQL string. However, this only works (at least on informix) if the input string is surround by single quotes in the SQL. Input strings surrounded by double quotes can not be escaped.

This method, combined with expanding function calls within strings, I was able to prevent SQL injection without major DBI and logic changes.

Been a while

Its been a while since my last post and a lot has happened since.

Recently (in my spare time) I have been focusing on the second version of my game engine, Mercury.
Development is picking up speed and the project is really taking shape. My personal goal is to get the engine functioning enough to make a few short games. The previous version of the engine was successfully used by the UMBC game development club for their year long 3D project. I'll probably start writing about graphics programming more than anything else.

Writing secure SQL applications

When writing applications that make use of SQL, specifically applications that live on the web, security should be a high priority. Unfortunately security usually ends up just an afterthought. In my experience reviewing and maintaining web applications written by others, I have found that they take little to no precaution against SQL injection.

SQL injection is the practice of crafting user input to alter the function of a dynamically generated SQL statement. In web based langagues, SQL statements are usually constructed using string concatenation to combine the query statements with the query values. This can lead to very dangerous conditions. Consider the following simple query.

select username from user_table where email='myemail@email.com'

Assuming the email address is inserted into the query using string concatenation it is trivial to alter how the query functions. If I entered my e-mail as:

myemail@email.com'; drop table usertable; --

The resulting query would be:

select username from user_table where email='myemail@email.com'; drop table usertable; --'

This would instruct the SQL server to drop the table (assuming the application has adequate permissions). Of course you can construct any statement you wish to manipulate the SQL server.

The usual protection against this type of attack to to escape special characters such as ' and ;. This can help improve security but is not fool proof.

Consider the following:

select username from user_table where id=123456

If the user id could be manipulated by the user it would be possible to make the id something like:

123456 and 1=(delete from user table where id != 123456)

The resulting query would be:

select username from user_table where id=123456 and 0<(delete from user_table where id != 123456)

This would instruct the SQL server to delete all users who's id is not 123456. Notice we have not used any special characters so escaping would not help in this situation.

Now there is a rather nice solution to these problems, parameterized queries. Parameterized queries allow you to prepare queries and then send in values at execution time.

Using the last example, the parameterized query would look like this:

select username from user_table where id=?

Parameters are usually indicated with a ? but may depend on the SQL library.The query is prepared by using something similar to:

$query = $db->prepare("select username from user_table where id=?");

We only need to do that once.

Then we can execute it as many times as we want with something similar to:

$result = $query->execute("123456");

The neat thing with this is that the database library will handle inserting the parameters into the query. There is no need to escape special characters using this method. I would argue this using this method makes SQL injection extremely difficult, if not impossible.

Using parameterized queries is usually a little more work than just concatenating strings, but the benefits are well worth the extra effort.

I have used parameterized queries using both PERL and PHP with Informix and mySQL databases. The PERL module is DBI, the php class is mysqli. They function differently but the concept is the same.