©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

Harden Your Applications to Combat SQL Injection

by Craig S. Mullins

An important aspect of database security is designing your applications to avoid SQL injection attacks. SQL injection is a form of web hacking whereby SQL statements are specified in the fields of a web form to cause a poorly designed web application to dump database content to the attacker. Stories abound in the news where SQL injection was used for nefarious purposes. Several high-profile cases over the past few years impacted government websites, Microsoft in the UK, a Swedish election, PBS (the Public Broadcasting System), and Lady Gaga’s website (among many others).

In order for SQL injection to succeed, the application code used by the website must be vulnerable to an injection attack. SQL injection relies upon programs that do not adequately filter for string literal escape characters embedded in SQL statements or where user input is not strongly typed. So instead of inputting data into a form, SQL statements are supplied. The SQL is "injected" from the web form into the database causing it to be executed and access (or even modify) unintended data.

Perhaps it is easiest to comprehend SQL injection by example. Consider a web-based application using dynamic SQL. The website requires users to login with their e-mail address and a password. Almost all sites of this type also offer an option to retrieve your password by supplying your e-mail address. Perhaps the SQL looks something like this:

          SELECT userid, password
          FROM uid_pwd_table
          WHERE field = '$EMAIL';

The variable $EMAIL represents the input from the form on the website. A savvy hacker can attempt a SQL injection attack by entering:

          anything' OR '1'='1

If the application does not check the input properly the injection causes the SQL to now look like this:

          SELECT userid, password
          FROM   uid_pwd_table
          WHERE  field = 'anything' OR '1'='1';

Executing this statement causes a complete dump of every userid and password in the database because the OR '1'='1' component will always evaluate to TRUE. It does not matter what the first part of the injection was, it could be anything, because the second part of the injection gives the hacker everything in the table.

Another form of SQL injection relies upon improper typing, for example not checking whether data that should be numeric is actually numeric. Consider, for example:

          statement := "SELECT * FROM userinfo WHERE id = " + in_var + ";"

In this case, the SQL is being built into the statement variable; in_var is the variable used to supply the input. Let’s assume that the id column is numeric. However, if the program does not check the data type of the in_var variable to ensure that numeric data is supplied, SQL injection can occur. For example, instead of just supplying a numeric value, the hacker can supply something like this:

          4;DROP TABLE customer

SQL Injection Prevention

Using well designed query language interpreters and coding applications appropriately can prevent SQL injection attacks. When possible use static SQL instead of dynamic SQL to improve the security of your database applications and data. Static SQL is hard-coded into the application and cannot be changed at runtime. Dynamic SQL is flexible and can change at runtime. When the SQL can change at runtime, a sufficiently motivated and skilled hacker can potentially change the SQL or potentially deploy a SQL injection attack to gain access to unauthorized data.

Static SQL is common in mainframe DB2 applications, but not so much for other platforms and database systems. The DB2 bind command "hardens" the SQL and optimizes access to the data.

Always validate user input by testing type, length, format, and range. The program should make absolutely no assumptions about the data that is received. Test the size and data type of input and enforce appropriate limits. Doing so can help to prevent buffer overruns. Test the content of string variables and allow only expected values to be processed. Any input that contain binary data, escape sequences, and comment characters should be summarily rejected.

Avoid concatenating user input that has not been validated. String concatenation is the primary point of entry for SQL injection attacks. Furthermore, consider using stored procedures to validate user input.

Analyze input and reject anything that contains special characters such as the semi-colon (;), the string delimiter ('), comment delimiters (--, /*…*/), V$ (the beginning of Oracle DBA views), and xp_ (the beginning of SQL Server catalog stored procedures).

With foreknowledge of SQL injection techniques and proper development procedures, all SQL injection attacks can be prevented.

Summary

Understanding SQL injection techniques and coding your database applications appropriately is an important aspect of modern database security.

From Database Trends and Applications, April 2012.

© 2012 Craig S. Mullins,  

April 2012