Preventing SQL Injection in PHP

SQL Injection is a vulnerability that allows an attacker to insert or inject a SQL query into an application. Injection is number one vulnerability on the OWASP Top Ten list for 2013. Common ways to exploit this vulnerability is to add a SQL statement into a form element or by sending POST/GET requests with the query using known parameters. The risk of SQL injection is loss or compromise of critical or sensitive data.

Understanding SQL Injection

Here’s an example of how a SQL injection attack works. Say there is a simple login form on a page like this.

1 2 3 4 5 6 7 8 9

<form method="POST" action="mypage.php">   <label for="username">Username:</label> <input id="username" name="username">   <label for="password">Password:</label> <input id="password" name="password">   </form>

The PHP to process this form looks like this.

1 2 3 4 5 6

$username = $_POST['username']; $password = $_POST['password'];   $sql = "SELECT * FROM users WHERE username = '{$username}' AND password = '{$password}'";   // connect to database and run query...

Note: This is only a simplified example, real passwords should not be stored in plain text.

This script works great if all users have good intentions, but unfortunately that’s not always the case. Here’s how an attacker can exploit this simple login form to takeover the database.

Appending SQL to the Query

Using SQL injection an attacker can add additional harmful queries. If an attacker uses the following password they could drop the user table entirely.

1	notThePassword'; DROP TABLE users WHERE '1' = '1

As you can see, the SQL statement in the $sql variable is closed out with a closing apostrophe and semicolon. A second statement is added after that drops the users table.

1=1 Always Evaluates True

Using SQL injection an attacker can modify the query to always evaluate true. An attacker could also use SQL injection to gain access to any user’s account with the following password
 

1	notThePassword' OR '1' = '1

The OR clause has been added here which will always evaluate to true and allow an attacker to successfully login. This is the completed SQL statement after the injection attack.

1	SELECT * FROM users WHERE username = 'username' AND password = 'notThePassword' OR '1' = '1'

Prevent and Secure Against SQL Injection

Fortunately, SQL injection is very easy to prevent once you understand the vulnerability. There are two steps to preventing an injection attack. The first step is proper user input validation and sanitization. The second step is to update the database technology to use prepared statements or to properly escape the input added to the query.

Validation and Sanitization

The first step to preventing SQL injection is to properly validate and sanitize user input. In the login form example, there are likely some characters that can be treated as illegal, depending on your application. For instance, usernames could be limited to letters and numbers only, limiting the attack surface. Passwords on the site should be hashed which would address the threat of special characters posing an injection risk.

The key takeaway is to always know what data you are expecting the user to enter, if it’s a number then validate that it’s a number and maybe even cast it to a float or integer to be safe. If characters are not allowed, then remove them before adding the user input to a query. This will also help maintain data integrity.

Using Prepared Statements or an Escaping Strategy

The second step to preventing SQL injection is to use prepared statements or to use an escaping strategy appropriate for your database.

If your application is a new application then consider using the PHP mysqli or PDO libraries. These both allow the use of prepared statements. Prepared statements are like a template for the SQL statement with placeholders for any variables or parameters. The database will handle adding the parameters safely back into the query. Prepared statements are not vulnerable to SQL injection IF all user input is added using parameters in the query.

In many applications prepared statements are not an easy option, as is the case with many legacy applications. An alternative to prepared statements is to properly escape all user input prior to adding to the query. This should be specific to the database and library used. If your application uses the PHP mysql extension, then there is a function to properly escape user input: mysql_escape_string().