SQL Injection

SQL injection is a security threat that appears wherever a PHP script is presenting data from a database, taking as input some identification of the content that needs to be presented, for instance with content management systems, or even simple scripts that return simple data. As long as input identification is inserted into SQL for data retrieval, the threat exists!

Let's look at a simple example. Let's say that you have a script that presents web pages according to user selection which is passed through URL variable 'page=':

index.php?page=links

This script then takes page identifier in variable 'page' and puts it into SQL query to fetch the page with that identifier:

PHP:

$page= $_GET['page'];
$res= mysql_query("SELECT FROM table_with_pages WHERE page_id='{$page}' LIMIT 1");
?>



The above is very common example of content retrieval. And a very vulnerable one. All it takes for the attacker is to bypass your single quotes and insert some malicious code. And all the attacker needs is to pass one simple string through the URL:

index.php?page=%27%3B%20DROP%20DATABASE%20--

The above is valid URL request, and server will parse the encoded characters into '; DROP DATABASE which effectively closes the single quotes of your original query, ends it with a semicolon and inserts new directive that drops your entire database and comments out everything else you may have in your SQL. And buh-bye database! Gone. Deleted.

Of course, this is a blatant example that does not need to work, especially if user-level the script is using does not allow dropping entire database. But, it can do a variety of other things, like selecting usernames or passwords, or inserting passwords where ID=1 which is usually the admin's user id, etc... Or it can bypass WHERE clauses with simple 'OR WHERE 1=1' and avoid password checks for example, or any other data retrieving filter you may have had, especially because data passed through URLs for content retrieval is usually a filter in a WHERE clause.

Naturally, it is difficult for the attacker to know the structure of your tables and fields if he/she did not see the code. With some guessing and luck he or she may be able to extract the structure, especially if your scripts visibly report errors (which is another security threat we'll deal with later). But what about open-source projects where code is available for everyone? Incidentally, open-source projects are frequent targets of attackers that seek loopholes in thousands of lines of code managed by many people who can easily omit a hole or two.

The most simple way to protect against such SQL injections is to escape sensitive characters like single or double quotes. It is best to use database-native functions for that, and in the case of our example, and MySQL, the proper handling would be:

PHP:

$page= mysql_real_escape_string ($_GET['page']);
$res= mysql_query("SELECT FROM table_with_pages WHERE page_id='{$page}' LIMIT 1");
?>



One much better way to protect against SQL injections is to use numeric identifiers wherever possible, so instead of index.php?page=links use ?page=1 and cast all data from this variable into integer:

PHP:

$page= (int) $_GET['page'];
$res= mysql_query("SELECT FROM table_with_pages WHERE page_id={$page} LIMIT 1");
?>



Another way is to filter the variable for allowed characters. For simple identifiers, allowed characters are usually alphanumeric (a-z0-9), which can be a case with simple page retrieval. Filter with preg_match:

PHP:

$matches= array();
preg_match ('/^([a-z0-9])$/i', $page, $matches);
//Find page identifier in $matches[1]
?>



And of course, keep your code to yourself if at all possible. So, to recapitulate, to protect against SQL-injections, very useful methods are:

* Using integers as data identifiers wherever possible
* Escaping all string inputs that may contain sensitive characters
* Filtering input identifiers for allowed characters
* Keeping the code to yourself



As a final note, since we used $_GET variable for example, same threat exists for POST-ed data. Since http request headers are text based, over TCP/IP type connection, it is very easy for the attacker to write a program (in C for example) that opens a connection to your script and sends malicious requests through POST'ed data.