What's the Big Deal with SQL Injection?

SQL injection, or SQLi, has received a lot of attention because it has been identified as one of the most common methods of attack on website infrastructure in both frequency and extent of damage. However, all is not lost. The threat posed by SQLi can be significantly reduced by using just a few easy to implement coding standards when building web applications.

What is SQLi?

In it's most basic form SQLi is where a user enters SQL code into a web form to change the behavior of a web application usually in an effort to retrieve or destroy data. This means that SQLi is only possible if a site creates dynamic SQL statements from user supplied input to be executed by a server side database management system (DBMS). Any database driven website will most likely do this to log in users or retrieve information so this is a very common problem indeed. There are many forms of SQLi from obvious to extremely obscure, but they are all related to the fact that by default SQL interpreters parse whatever query string is sent to them. A simple example will make this vulnerability much easier to grasp.

Let's say that a website is running the PHP language on it's server and that it logs in users by checking a submitted form's user name and password fields against a database table called users. If that user name and password combination exists within the database the the program will then create a session and a session variable to identify that user based on the database query results.

<form action='php/user_login.php' method='post'> <label for='un'>User Name:</label><input type='text' id='un' name='un'> <label for='pw'>Password:</label><input type='password' id='pw' name='pw'> <input type='submit'> </form>

A basic HTML form that allows an anonymous user to login.

$user_name = $_POST['un']; $password = $_POST['pw']; $query = 'SELECT user_id FROM users WHERE user_name = "' . $user_name . '" AND password = "' . $password . '"';

A basic PHP script that receives a user name and password from a website and uses these strings to construct a query.

Now, it is expected that the user will enter a user name and password like 'Dustin' and 'Sakura' which would create the following query.

SELECT user_id FROM users WHERE user_name = "Dustin" AND password = "Sakura"

If this user exists and the password is correct they will be 'logged in' other wise they will be given an error message. However, what if a 'black hat' hacker used " or 1=1 LIMIT 0,1 -- for the user name and password for the password. This would create the following query.

SELECT user_id FROM users WHERE user_name = "" or 1=1 LIMIT 0,1 --" AND password = "password"

This query would log the hacker in as the first user in the database! They could then iterate over each user by adjusting the LIMIT parameters allowing them to extract all of the information for every user in the database. Ouch.

While not possible with PHP some languages support batch queries which are even more dangerous. Here is an example where a hacker has entered "; DROP TABLE users -- for the user name and password for the password which creates the following query.

SELECT user_id FROM users WHERE user_name = ""; DROP TABLE users --" AND password = "password"

Say godbye to the users table, because it was just deleted!

How to Prevent SQLi

So we all know now that SQLi is very common and very bad. So how can it be prevented? The following list shows the best steps to be taken to prevent SQLi from most important to least important, but remember these should all be done!

Use Prepared Statements

Also called parameterized queries, prepared statements are a way to tell the SQL interpreter what parts of a submitted query should be treated as commands and what parts should be treated as data. This is the single most important step that can be taken to prevent SQLi. In PHP a prepared statement would be created like this.

$query = $db->prepare("SELECT user_id FROM users WHERE user_name = :user_name AND password = :password"); $query->bindParam(':user_name', $un); $query->bindParam(':password', $pw);

A simple prepared statement in PHP to log in a user.

The submitted strings will be treaded as string literals and not control characters. So, if a hacker sent " or 1=1 LIMIT 0,1 -- to this code as the user name the DBMS would search the users table for a user name that matches this string, which of course it would not. Problem solved.

Use Stored Procedures

Stored procedures are very similar to prepared statements in that they separate user supplied data from program control so that all submitted data is treated as just data. However, stored procedures are created, compiled, and stored for later use. So if a program has a pagination script that creates a query in the exact same way over and over again a stored procedure could be created which will provide the security benefits of a prepared statement in addition to the performance benefits of compiled queries stored on the server. Stored procedures also help with the don't repeat yourself (DRY) principle by centralizing common SQL code and establishing a common API for programs to use. Stored procedures should not be used for creating dynamic SQL commands under ordinary circumstances, such as if a user is searching a database using numerous optional fields to create a query such as SELECT * FROM products WHERE cat='jewelry' AND sub_cat='engagement_rings' AND dia_size BETWEEN 1.0 AND 1.5. Here is a simple example of a stored procedure for displaying a user's information and how it could be accessed from a PHP script. It takes a single parameter, a user id, and then returns all of the users information.

DELIMITER // CREATE PROCEDURE get_user_info (IN u_id INT) BEGIN SELECT * FROM users WHERE user_id = u_id; END // DELIMITER ;

An example of a creating a simple stored procedure in SQL.

$user_id = $_POST['uid']; $query_result = mysqli_query($db, "CALL get_user_info($user_id)");

An example of a simple PHP script that accesses a stored procedure with a supplied user id.

Escape Input

Before prepared statements became all the rage many programmers used white or black lists to determine which characters should be removed or escaped before being sent to a DBMS. While this method is prone to error and is not a proper replacement for prepared statements it is still a good practice. Most DBMS have commands that can be ran on incoming data to escape control characters before being appended to a query string. In PHP two popular commands are addslashes and mysqli_real_escape_string which both escape special control characters before being sent to the DBMS. Using the example from earlier if a user submitted " or 1=1 LIMIT 0,1 -- for the user name it would be converted to the following \" or 1=1 LIMIT 0,1 -- which would result in a query of SELECT user_id FROM users WHERE user_name = "\" or 1=1 LIMIT 0,1 --" AND password = "Sakura" which would obviously return zero results, which is the behavior that is desired.

Also, remember to escape data for any HTML which a hacker my try to inject into data that will then be displayed and possibly ran later when accessed. In PHP the htmlspecialchars function is used to filter HTML data. If a hacker submitted <script type='text/javascript' src='destroy.js'></script> it would become the innocuous string <script type='text/javascript' src='destroy.js'></script>

Again it is all about separating literal values from code and making sure that an application communicates these differences to the software it connects to whether it is a DBMS or browser.

Validate Input

Even when using prepared statements all user supplied data should be checked to make sure it is well formed and matches what is expected. This protects the integrity of the database by only allowing proper data to be inserted and also prevents code from being inserted into text fields, saved into a database, and then displayed and unintentionally ran by other users once accessed by a script later. Also, data should always be validated at the server even if it is already validated by a JavaScript API. A hacker could easily circumvent JavaScript validation and submit malformed data to the server. Some important checks are:

Size or Length. Is the string or number submitted the right length? For example a SHA-1 digest should be forty characters in length. Also, database schema often place limits on each column so if data is submitted which has a length greater than that taken by the database how should that data be handled? Should it be truncated, generate an error, or allow for the field to be modified? What would happen if a user submitted a binary file in the megabytes for a text field?

Type. If a program is expecting a number it should not accept a string and certainly not any code.

Business Logic or Patterns. Phone numbers should be ten digits possibly separated by parentheses or dashes, names should be alphabetic, and so on. Make sure all submitted data matches the patterns expected.

Use Custom Error Statements

Custom error statements should be used when trying to run queries against a DBMS. This prevents intimate details about the database configuration from being displayed to hackers who can then use this information to help expedite their efforts. Usually error checking would be done within try catch blocks, but here is a very common way in which PHP scripts 'catch' SQL errors and generate a custom error page

// this query will fail // $query = 'SELECT * FROM TABLE users where user_name="Dustin" and password="Sakura"'; $result = mysqli_query($db, $query); if($result === false) { display_failed_query_page(); }

An example of simple error catching in PHP when doing SQL queries.

Give Least Privilege to Database Users

Taking the steps above will prevent almost all potential SQLi, however, there are many very sophisticated ways to hack servers of which SQLi is only one. To help protect against unknown future threats posed by software bugs or other avenues of attach always give the least privileges possible to users that access a database. If part of a program is used to only view data then create a seperate database user for that program that can only view data and not alter, add, or delete data. Also, if a script only needs access to a certain table or maybe just a part of a table limit that program's dabase user to only that table or even a view of the table. This limits the access of programs which access a database and therefore how much data is exposed to unauthorized access or deletion.

Conclusion

So while SQLi is a major threat to servers everywhere it is not an unmanageable threat. If all programs that accept user input to generate dynamic SQL statements use prepared statements, stored procedures, input escaping, input validation, custom error statements, and the principle of least privilege then SQLi will go from big deal to no problem.