Prevent SQL Injection Attacks

Applications which return different results based on user input (such as logging a user in based on username/password) may not properly filter all user input. Thus, when a user enters some data, they may modify the data in an attempt to change the underlying SQL query against the database. If they succeed, then the attacker may be able to log in as an administrator, or steal other users data.

Imagine that you have user data stored in a database tables (users), and it has the following structure

CREATE TABLE users(
	User_id INT,
	username VARCHAR(30),
	password VARCHAR(30)
	);

Looking at the code the site uses to log in, it dynamically builds a SQL query in PHP, after a user has given username and password in a form (with user and pass variables):

<?php
$results = mysql_query( "SELECT use_id FROM users WHERE username='".$_POST['user']."' AND password='".$_POST['pass']);
?>

The main problem above is that data from the user is not sanitized; the POST variable is being used to pull data directly from the login form, and placing it into the query. At this point, an attacker could try putting in a special string for username:

' or 1=1 or '

Now the query always returns the first user (admin potentially) and the attacker is logged in as an administrator!

Other attacks are possible using similar logic. Instead of logging in as an administrator, an attacker could use other special strings to request data from the database they should not have access to, potentially gathering all the data in the database which the application database user has access to.

A successful attack would allow an attacker to steal most or all of the database, including all sensitive data, and log in with administrative privileges in the application.

SQL Injection is one of the most high risk vulnerabilities an application can have.

SQL Injection can be prevented in a similar way to XPath injection. The best way is to carefully sanitize user input. Any data received from a user should be considered unsafe. Removing all single and double quotes should remove most types of this kind of attack.

Beware that removing quotes can have side effects, such as when usernames might contain valid quotes. Imagine common names such as O'Malley, which contains a legitimate quote and may be input on a name request form. In these cases, the input should be escaped, often by adding a \ in front of quotes. Check with your specific library and database software for the proper syntax.

Most programming libraries contain functions to help escape user input for the sake of queries. Check your specific documentation for the correct way to automatically escape data for querying SQL Databases. In PHP and MySQL, the function mysql_real_escape_string to escape dangerous query data. Using this or similar functions on all user input will help to sanitize code.

Secondly, write all SQL queries using parametrized queries, where the query is pre-defined, and strongly typed data is passed in after the fact. In PHP for example, you could re-define the query seen previously with this parametrized query to prevent SQL injection attacks:

<?php
	$query= "SELECT user_id FROM users WHERE username=? and password=?";	//query definition
	$preparedStatement=$database_connection()->prepare($query);				//prepare the statement
	mysqli_stmt_bind_param($preparedStatement, 'ss', $field1, $field2);		//prepare to bind two Strings (the ss)
	$field1 = $_POST['user'];			//you may want to do more input checking here!
	$field2 = $_POST['pass'];			//you may want to do more input checking here!
	mysqli_stmt_execute($preparedStatement);	//execute the parametrized query
?>

SQL Injection Cheat Sheet
Prevent SQL Injection in PHP with MySQL
SQL Injection by Example

Golem Technologies includes numerous different server setting scans to help you reduce your exposure to attack with thorough security scanning, including SQL Injection. See how the Golem Scan can help your business today.