How To Secure Your Database

SQL injection is done without the administrator’s knowledge or permission by inserting a MySQL statement into the database. An example of how this is done; when requesting user input i.e. ‘customer id’, instead of providing this information the hacker inserts a MySQL statement that is then executed without you being aware.

Here is an example of a string displaying the difference between regular interaction and SQL injection; this allows the hacker to gain access to records:

The user is requested to provide their customer pin, this is interpreted into a SELECT statement providing the necessary information.

// regular interaction customer pin

$pin = "12345";

$query = "SELECT * FROM customers WHERE pin = '$number";

echo "Normal: " . $query . "<br />";

// SQL Injection

$pin_bad = "' OR 1'";

// MySQL query builder – not very secure

$query_bad = "SELECT * FROM customers WHERE pin = '$pin_bad'";

// show the query with injection

echo "Injection: " . $query_bad;

Normal: SELECT * FROM customers WHERE pin = '12345'

Injection: SELECT * FROM customers WHERE pin = '' OR 1''

The regular interaction does not create a problem, given that the MySQL statement will choose information from customers that have a pin equivalent to 12345.

The SQL injection caused the query to behave in a way that was not intended via a single quote (’) the string part of the MySQL query was brought to an end

pin = ' '

and then added on to our WHERE statement with an OR clause of 1 (always true).

pin = ' ' OR 1

Example 2:

DELETE statement: Below is an example of where a hacker can remove all information from the “customers” table.

$id_evil = “‘; DELETE FROM customers WHERE 1 or userid = ‘“;

SELECT * FROM customers WHERE userid = ' ';

DELETE FROM customers WHERE 1 or userid = ' '

PHP has a function to assist in the prevention of this known problem: mysql_real_escape_string. This function acts by replacing the (’) quotes safe alternative i.e. (’) known as an escaped quote.

The example below demonstrates how the function can be used to prevent example 1 and 2:

//Note: To use the function please ensure you are connected to your MySQL database.

$id_bad = "' OR 1'";

$id_bad = mysql_real_escape_string($id_bad);

$query_bad = "SELECT * FROM customers WHERE userid = '$id_bad'";

echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";

$id_evil = "'; DELETE FROM customers WHERE 1 or userid = '";

$id_evil = mysql_real_escape_string($id_evil);

$query_evil = "SELECT * FROM customers WHERE userid = '$id_evil'";

echo "Escaped Evil Injection: <br />" . $query_evil;

Escaped Bad Injection:

SELECT * FROM customers WHERE userid = '' OR 1''

Escaped Evil Injection:

SELECT * FROM customers WHERE userid = '';

DELETE FROM customers WHERE 1 or userid = ''

The SQL injection attack has been prevented i.e. the backslash ensures that the evil quotes have been escaped and the remaining queries will be looking for a nonsensical userid:

Bad: ' OR 1'

Evil: '; DELETE FROM customers WHERE 1 or userid = '