There are scenarios where databases can become vulnerable to hackers, for example, taking raw data and inserting it into a MySQL table creates a security vulnerability called SQL injection. These situations can be prevented by securing scripts and MySQL statements.
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.
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 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
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 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;
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 = '