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.