SQL injection

The article "Ensuring website safety" is provided by Sophos Plc and SophosLabs.

December 2007

The SQL injection is used to attack sites that work with databases [14]. The SQL injection becomes possible when the SQL queries use unfiltered user input.

The SQL queries are used to retrieve data from the database, add it to the database, as well as to modify and delete the data in the database. Many sites use scripts and SQL for dynamic content pages. SQL queries often use user-entered data. This can cause security risks because hackers can try to inject malicious SQL code into the input data. Without adequate protection measures, such code can be successfully executed on the server.


Consider the following PHP code:

$firstname = $_POST["firstname"]; mysql_query("SELECT * FROM users WHERE first_name='$firstname'");

After the user enters their name in a form, the SQL query returns the list of all users with the same name. If someone enters “Chris” in the form, the SQL query will look like this:

"SELECT * FROM users WHERE first_name='Chris'"

This is a valid structure that will work as expected. But what happens if you enter, for example, “'; drop table; #” instead of the name? Then the structure will look as follows:

"SELECT * FROM users WHERE first_name=''; DROP TABLE users; #'"

The semicolon allows you to execute several successive commands. As a result of a simple SQL command turns into a complex threesome structure:

SELECT * FROM users WHERE first_name='';

Initial instruction is now useless, it can be skipped. The second instruction will delete the whole table from the database. The “#” character from the first row will make MySQL ignore the rest of the string.

This vulnerability is especially dangerous since it can be used to retrieve the closed data, to modify some fields or remove information. Some DBMSs also allow you to execute system commands using SQL.

Fortunately, you can easily eliminate this type of threat by validating the user input. PHP features a special mysql_real_escape_string function that removes the potential SQL injection code from the string. You should use it to filter all the data injected in the SQL instruction.