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.

<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.

$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.

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
 

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.

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().

2 comments on “Preventing SQL Injection in PHP

  1. Nathan Mobrex says:

    I’ve been studying SQL injection for the past few days and I have a question that may help me understand this type of attack and more. How, exactly, would the attacker present that malformed code? Would possible techniques including scraping content and executing from another domain under the hackers control? Or would the first step be gaining access to the domain using fraudulent user name/passwords? Or is this attack limited through inserting commands into the URL bar of a website after you have determined it is vulnerable? Are any of these techniques correct, or am I in the woods? I know this is probably very basic and that’s why it’s not even mentioned in this article (and many others like it). However, I would like to know how the process is performed.

  2. SEO Perth Company says:

    I have read some good stuff here. Certainly worth bookmarking for revisiting.
    I wonder how much effort you place to create this type of wonderful informative web site.

Comments are closed.