Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== SQL Injection - Example attacks - Basic SQL Injection attack with defence ======

===== SQL injection example =====

Here is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.

MySQL & PHP Code:

<code php>
// A good user's name.
$name = "john"; 
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "<br />";

// User input that uses SQL Injection.
$name_bad = "' OR 1'"; 

// Our MySQL query builder, however, not a very safe one.
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

// Display what the new query will look like, with injection.
echo "Injection: " . $query_bad;
</code>

Displays:

<code sql>
Normal: SELECT * FROM customers WHERE username = 'john'
Injection: SELECT * FROM customers WHERE username = '' OR 1''
</code>

The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to john.

However, the injection attack has actually made our query behave differently than we intended.  By using a single quote (') they have ended the string part of our MySQL query.

  * username = ' '// // and then added on to our WHERE statement with an OR clause of 1 which is always true. 
  * username = ' ' OR 1// This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!// 


==== A more serious SQL injection attack ====

Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse.  For example an attacker could empty out a table by executing a **DELETE** statement.

MySQL & PHP Code:

<code php>
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

// Our MySQL query builder really should check for injection.
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

// The new evil injection query would include a DELETE statement.
echo "Injection: " . $query_evil;
</code>

Displays:

<code sql>
SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '
</code>

If you were run this query, then the injected DELETE statement would completely empty your "customers" table.  Now that you know this is a problem, how can you prevent it?


==== injection prevention - mysql_real_escape_string() ====

This problem has been known for a while and PHP has a specially-made function to prevent these attacks.  All you need to do is use the function **[[http://php.net/mysql_real_escape_string|mysql_real_escape_string]]**.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and returns the same string with all SQL Injection attempts safely escaped.  Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.

Lets try out this function on our two previous injection attacks and see how it works.

MySQL & PHP Code:

<code php>
//NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'"; 
$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 
$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;
</code>

Displays:

<code sql>
Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\' OR 1\''
Escaped Evil Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''
</code>

Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack.  Now all these queries will do is try to find a username that is just completely ridiculous:

  * Bad: \' OR 1\'
  * Evil: \'; DELETE FROM customers WHERE 1 or username = \'

The handy **mysql_real_escape_string()** function has helped prevent SQL Injection attacks on your websites. 


==== injection prevention - MySQLi ====

The **mysql_real_escape_string()** function has been obsoleted in more recent versions of PHP.  MySQLi supports parameterized queries.  These days the mysql_real_escape_string option would only make sense for legacy code on an old version of PHP.

Instead of 

<code php>
$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
</code>

use this:

<code php>
<?php
  $mysqli = new mysqli("server", "username", "password", "database_name");

  // TODO - Check that connection was successful.
  $unsafe_variable = $_POST["user-input"];

  $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

  // TODO check that $stmt creation succeeded.

  // "s" means the database expects a string
  $stmt->bind_param("s", $unsafe_variable);
  $stmt->execute();
  $stmt->close();
  $mysqli->close();
?>
</code>

The key function to read up on there would be [[http://php.net/mysqli.prepare|mysqli::prepare]].

A better alternative is to use [[http://php.net/pdo|PDO]].