Secure your Website from SQL Injections

What is SQL Injection

A SQL injection is often used to attack the security of a website by inputting SQL statements in a web form to get a badly designed website to perform operations on the database (often to dump the database content to the attacker) other than the usual operations as intended by the designer. source: http://en.wikipedia.org/wiki/SQL_injection

Most of the SQL Injections web attacks comes from insecure forms. When someone submits a form, he can use, most of the time, any available characters and put inside the fields whatever he wants. This way, he can "inject" bad code and hope for an insecure process to get into the system.

Here some examples of what he can inject:

• ' or 1 = 1; /* '
• ' or 1 = 1; DROP TABLES `table`; /* '
• ' or 1 = 1; SELECT SLEEP(86400); /* '

The first code will let the unwanted user to the system without having any valid credentials. The second one will allow him to drop any tables (if of course the permission is set, which sometimes all permissions are granted to the MySQL user) and the third one will execute the first query and in addition wait for the query to be completed in 86,400 seconds (or 1 day).

How this works exactly?

Let's use the classic login example. So on the back-end we will get something like this:

$username = $_POST['username'];
$password = $_POST['password'];
mysql_query("SELECT * FROM userTable WHERE username = '$username' AND password = '$password'");

If you insert malicious code, let's take the first example listed above, and use this code as the username.

mysql_query("SELECT * FROM userTable WHERE username = '' or 1 = 1; /* '' AND password = '$pw'");

As you can see, the query will check if the username is empty (which will return false) and check if 1 is equal to 1, which will always be true, and then ignore the rest of the query because he uses the ; to terminate the query and comment out the rest using /*.

How to prevent this

use mysql_real_escape_string()

The first thing you can use is the mysql_real_escape_string() function. This will escape the special characters in a string for use in an SQL statement. For example, it will escape the single quotes (') and replace them with \'.

This will replace ' or 1 = 1; /* ' to \' or 1 = 1; /* \'. Therefore, fix the query string to:

$username = mysql_real_escape_string($_POST['username']);
mysql_query("SELECT * FROM userTable WHERE username = '\' or 1 = 1; /* \'' AND password = '$pw'");

Clean the inputs

Another solution is to clean the inputs. This practice basically removes unwanted chars and convert the special chars in HTML format.

function clean(&$var, $default = '') {
  if(!isset($var)) {
    $var = $default;
    return;
  }
  $var = preg_replace("/[^a-zA-Z0-9@\"'\.\-_\s]/", "", trim($var));
  $var = htmlentities($var, ENT_QUOTES);
}

For example, this will convert the single quotes ' to '.

This way, the string will look like:

$username = clean($_POST['username']);
mysql_query("SELECT * FROM userTable WHERE username = '' or 1 = 1; /* '' AND password = '$pw'");

Prepare the statements

This will prepare an SQL statement for execution. The SQL statement will be sent to the database and parsed separately.

$data = array(
 ':username' => $_POST['username']
 ':password' => $_POST['password']
);
$statement = $databaseConnection->prepare('SELECT * FROM userTable WHERE username = :username AND password = :password');
$statement->execute($data);
$rows = $statement->fetchAll();

This will automatically make all your variables safe for query execution.