How to secure your database

There are scenarios where databases can become vulnerable to hackers, for example, taking raw data and inserting it into a database table creates a security vulnerability called SQL injection. These situations can be prevented by securing scripts and database statements.

SQL injection is done without the administrator’s knowledge or permission by inserting a database 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 database statement that is then executed without you being aware.

Example:

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;

Display:


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 database 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 database 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

All entries in the “customers” table selected as a result of this statement because OR clause of 1 is true.

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 = ‘“;


// SQL injection to be detected by the MySQL query builder
$query_evil = "SELECT * FROM customers WHERE userid = '$id_evil'";// DELETE statement should form part of the new evil injection query
echo "Injection: " . $query_evil;

Display:


SELECT * FROM customers WHERE userid = ' ';
DELETE FROM customers WHERE 1 or userid = ' '

Prevention:

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 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;

Display:


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 = '

Related Posts: