Share this page: Follow me to be notified of new articles:

SQL Injection with MySQL

Introduction

This is just a basic introduction to SQL injections in MySQL. If your data are valuable, I strongly recommend you to work with a database administrator who will help you to optimize your database security.

Try it yourself

If you want to try yourself the following examples, just create this table with 3 entries :

What is an SQL injection?

An SQL injection is a result of a code injection in a SQL query. A code can be injected because the script that was used was not correctly protected.

For our test, we will use the table "account_login" with 3 users (the schema is available above):
mysql> SELECT * FROM account_login;
+----+------------+-----------+
| id | user_login | user_pass |
+----+------------+-----------+
|  1 | Gab        | password  |
|  2 | Paul       | 123456    |
|  3 | Robert     | 12345678  |
+----+------------+-----------+
3 rows in set (0.00 sec)

Now, here is an unsecure PHP script :


Why is it insecure?
It's insecure because the datas you get from the FORM are directly put in the MySQL query, without any form of control.
If a user put the login "Gab" and the Password "password", the query will find his ID :
mysql> SELECT id FROM account_login WHERE user_login = 'Gab' AND user_pass = 'password';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)


Now, if I do the same test with an incorrect password like "fish", I won't fine any result :
mysql> SELECT id FROM account_login WHERE user_login = 'Gab' AND user_pass = 'fish';
Empty set (0.00 sec)


That's just logical.
Now, if the user put "' OR '1" in the password input, we will have the following result :
mysql> SELECT id FROM account_login WHERE user_login = 'Gab' AND user_pass = '' OR '1';
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

Examples of injections usually used to log into another user account

Here are a few examples of datas that can be injected depending on the initial query : You can also just use this examples to comment the end of the query : Let's say that the user have really bad intentions, he could even put "a';DROP TABLE account_login; SELECT '1" in the password input :
mysql> SELECT id FROM account_login WHERE user_login = 'Gab' AND user_pass = 'a';DROP TABLE account_login; SELECT '1';
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT * FROM account_login;
ERROR 1146 (42S02): Table 'test.account_login' doesn't exist

It would drop the table "account_login" ! The only requirement would be for him to know the table name : he could guess it or eventually directly on your website the server displays the MySQL errors.

Blind SQL injection

When the attacker is not able to see the result of the injection (MySQL errors are not displayed) [bla bla bla] http://ha.ckers.org/sqlinjection/ http://en.wikipedia.org/wiki/SQL_injection

PHP solutions

The bad solution : addslashes();

addslashes(); is the most commonly used function to escape characters, it means that it will add an "\" after the characters "'", """, "\" and the nul byte.

Here is an example :
<?php
$str = "Is your name G'ab?";

// Outputs: Is your name G\'ab?
echo addslashes($str);
?>

The newer solutions : mysql_real_escape_string(); and mysqli_real_escape_string();

They are more reliable than "addslashes();": PHP recommends to use mysqli_* instead of mysql_* because there is no more active developpments on mysql_* (just bug corrections). PHP indicates that mysql_* is a "Long term deprecation announced". Read more about choosing the right PHP/MySQL API.

Example with "mysql_real_escape_string();":
<?php
// MySQL Connextion
$conn = mysql_connect("MYSQL_SERVER", "MYSQL_LOGIN", "MYSQL_PASS") or die(mysql_error()); 
mysql_select_db("MYSQL_DATABASE") or die(mysql_error()); 

$Password = "pa\ssw'ord";

// Outputs: pa\\ssw\'ord
echo mysql_real_escape_string($Password, $conn);
?>

A more complete solution using a homemade function

The function mysql_my_escape_string(); will escape more characters than addslashes();, mysql_real_escape_string(); and mysqli_real_escape_string();. I recommend everyone to use it.

Example with "mysql_my_escape_string();":
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.