Preventing SQL Injection

What is SQL Injection?

Many sites use databases as a back-end to store their data, using queries to insert and select data from it. However, many people are unaware that such sites are often vulnerable to a form of attack called SQL injection.

SQL injection is when malformed user input is used directly and deliberately in an SQL query, in a way that allows the attacker to manipulate the query. This means that an attacker could delete portions of your database, make himself an admin account etc, the possibilities are endless.

One of the most common vulnerabilities is when logging in to a site. Take this example:

$username = $_POST['username'];
$password = $_POST['password'];

$result = mysql_query("
SELECT *
FROM
site_users
WHERE
username = '$username'
AND
password = '$password'
");

if ( mysql_num_rows($result) > 0 )
// logged in

This is vulnerably to a pretty obvious SQL injection; can you work out how an attacker could modify the query to allow himself to be logged in regardless of whether or not he has the right password?

If the attacker enters a valid username in the username field: "rob" and the following in the password field: ' OR 1=1 '

The resulting query will look like this:

SELECT *
FROM
site_users
WHERE
username = 'rob'
AND
password = '' OR 1=1

It will therefore select all users where:

• the username is "rob"
• either the "password" field is empty, or 1 is equal to 1

Since the last criteria will always be true—when is 1 ever not equal to 1?—, the user will be able to log in as rob without knowing rob's password !!

How can we prevent SQL injection attacks?

As with XSS attacks, you must never trust user input. The best way of cleaning user input is using PHP's built in mysql_real_escape_string() function; this will escape characters such as ', " and others, making them useless in "breaking out" of a quoted string as in the above example. If you're using a number in your query, then you should use intval() on the inputted number to ensure it is numeric.

I have also made the following function that can be used to discard any characters that can be used to manipulate the SQL queries. So, you can use this functions just as well to validate your SQL queries:

function is_valid($input)
{
$input = strtolower($input);

if (str_word_count($input) > 1)
{
$loop = true;
$input = explode(" ",$input);
}

$bad_strings = array("'","--","select","union","insert","update","like","delete","1=1","or");

if ($loop == true)
{
foreach($input as $value)
{
if (in_array($value,$bad_strings))
{
return false;
}
else
{
return true;
}
}
}
else
{
if (in_array($input,$bad_strings))
{
return false;
}
else
{
return true;
}
}
}

Function Usage

This is how you can use the above function:

if (is_valid($_REQUEST["username"]) == true && is_valid($_REQUEST["pass"]) == true)

{

//login now

}

However, don’t depend on this function alone, you must still use the mysql_real_escape_string() function in your SQL query. You can also modify this function or you can get a new idea to work out of this function.

Application Variables in PHP

If you have been doing development using ASP, you must be familiar with the Application variables. Application variables in ASP work very similar to the $_SESSION variable in PHP. However, unlike the $_SESSION variable, application variables are not specific to an individual user; they persist across every user of every page on the web site.

Benefits

The immediate benefits of application variables may not be apparent, but esperienced ASP coders understand their power. Take this one-liner, for example:

//You are visitor number echo $_APP["visitor_count"]++;

In fact, application variables have many other uses as well than what has been depicted in this article.

Solution

I looked on the web and read numerous other work-arounds for application variables in PHP, but found none that I liked. Every one required too much overhead code, or else multiple lines of code to actually use the variables. Here is my solution:

app.php

define("APP_DATA_FILE", "/tmp/application.data"); function application_start () { global $_APP; // if data file exists, load application // variables if (file_exists(APP_DATA_FILE)) { // read data file $file = fopen(APP_DATA_FILE, "r"); if ($file) { $data = fread($file, filesize(APP_DATA_FILE)); fclose($file); } // build application variables from // data file $_APP = unserialize($data); } } function application_end () { global $_APP; // write application data to file $data = serialize($_APP); $file = fopen(APP_DATA_FILE, "w"); if ($file) { fwrite($file, $data); fclose($file); } }

Usage

The usage of the $_APP variable is very similar to $_SESSION. Before using it on a page, you must include app.php and call application_start(). When you are finished, you must call application_end().

Sample PHP file using application variables:

include("app.php"); application_start(); //You are visitor number echo $_APP["visitor_count"]++; application_end();

Optimizations and Other Notes

Numerous improvements can be made to this code, but they are not included here for simplicity.

• For added security, permissions on the application data file should not allow any user, except for the web server, to read or write to it. Otherwise, if application variables contained sensitive data, such as passwords or credit card information, other users on the system could read this data.
• To keep from writing to disk unnecessarily, only call application_end() if data in the $_APP variable has changed.
• Another alternative is to let the application_end() function determine whether the $_APP variable has been changed, and only write it to disk if necessary. Simply make a copy of the $_APP variable in application_start(), then compare $_APP to the original in application_end().
• Finally, don't forget that unlike $_SESSION, the $_APP variable is only available in the global scope. When using $_APP from inside a function, be sure to include the statement global $_APP; at the top of the function.