How do I secure my database?
Print Email
Email this article
Close
Email this article
CloseYou are here: Home > Website Support > MySQL
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 = \'
Was this information helpful?
Yes NoThank you for your feedback
We are delighted to find that our article resolved your query.
Thank you for your feedback
We will resolve your query as soon as possible.
Please take a few moments to comment on your unresolved query. Simply tell us what your problem is. We guarantee that we'll get back to you within two hours (during office hours) in response to your query.
Related articles
-
Login Now
Our award winning proprietary control panel empowers customers to manage their hosting environment.
Take the Tour
