SQL Injection

SQL injection can be used to attack websites that interact with databases. It occurs when unfiltered input designated by the user is used in an SQL query. Many networks were breached using SQL injection vulnerabilities in Web servers to gain a beachhead. In Sept 2009, a 28-year-old Miami resident pleaded guilty on Friday to charges of conspiracy, computer and wire fraud, and aggravated identity theft stemming from the massive thefts of data from major commerce companies, such as retail giant TJX and payment processor Heartland Payment Systems.

SQL queries can be used to query a database, insert data into a database or modify/delete data from a database. A lot of modern websites use scripting and SQL to generate page content dynamically. User input is frequently used in SQL queries and this can be dangerous as hackers can try to embed invalid SQL code within the input data. Without careful attention, this malicious SQL may be executed successfully on the server. As example below, i would like to share my knowledge on how SQL injection was performed.

Take the following PHP code:
$firstname = $_POST[“firstname”];
mysql_query(“SELECT * FROM users WHERE first_name=’$firstname’”);
After submitting your first name to the web form, the SQL query will return a list of users that have your first name. If I put my name “Chris” in the form, the SQL query would be:
“SELECT * FROM users WHERE first_name=’Chris’”

This is a valid statement and will work as you would expect, but what would happen if instead of my first name, I put in something like “’; DROP TABLE; #”? The statement would then read:

“SELECT * FROM users WHERE first_name=’’; DROP TABLE users; #’”

The semi-colon allows multiple commands to be run, one after the other. Suddenly the simple statement is now a complex three part statement:
SELECT * FROM users WHERE first_name=’’;
DROP TABLE users;
#’

The original statement is now useless, and can be ignored. The second statement instructs the database to drop (delete) the entire table and the third uses the ‘#’ character which tells MySQL to ignore the rest of the line. The above is particularly dangerous and can be used to display sensitive data, update fields or delete/remove information. Some database servers can even be used to execute system commands via SQL.

Fortunately this type of vulnerability is easily avoided by validating user input. In PHP there is a special function for stripping out potential SQL injection code called ‘mysql_real_escape_string’. This function should be used to filter any data that is passed to an SQL statement.