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


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

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;


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

Was this answer helpful? 549 Users Found This Useful (0 Votes)