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-
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-
SELECT userid, password
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
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-
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-
With foreknowledge of SQL injection techniques and proper development procedures, all SQL injection attacks can be prevented.
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,