Protecting Innocent MySQL from SQL Injection

Introduction

 

This is a brief guide on how to protect your innocent MySQL database from SQL injection attacks.
 

What is SQL Injection?

 

As the name suggests, SQL Injection occurs when the user injects SQL statements into your application.
 
How does this happen?
 
Say we have a simple login form that takes a username and password, and validates against the database. If the username and password is validated, the user is logged into the system.
 

The code for this could look something like this:
 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
// get the user and password from the POST request
$user_name = $_POST[ 'username' ];
$password  = $_POST[ 'password' ];

$query = "SELECT first_name, last_name, account_number FROM users_table WHERE username = '$user_name' AND password = '$password' ";

$result = mysql_query( $query );

// check if mysql found anything, and get the record if it succeeded
if ( mysql_num_rows( $result ) > 0 ) {
    $data = mysql_fetch_assoc( $result );
    echo 'Welcome ' . $user . '!';
    echo 'Your account number is: ' . $data[ 'account_number' ] . '';
} //Print out the errorr message
else {
    echo 'Username or Password not valid! Please try again!';
}
?>

 

The above script works fine and it will allow the genuine users to enter their username and password to see their bank account number.
 

Let’s assume that I enter “demo” as my username, and “demopass” as my password, then the SQL query that is passed to the MySQL will looks like this:
 

1
SELECT first_name, last_name, account_number FROM users_table WHERE username = 'demo' AND password = 'demopass' ;

 

If our input data is validated successfully in the database, then we will be logged into the system and we will be presented with the account number else we will not get the access to the system and we will be presented with an error message “Username or Password not valid! Please try again!
 

The problem when a malicious user enters other characters that are meant to terminate the SQL statement.
 

Let’s assume that a user tries to login as username dem’o and password as demopass.
 

Now, let’s see how the SQL statement looks like.
 

1
SELECT first_name, last_name, account_number FROM users_table WHERE username = 'dem'o' AND password = 'demopass' ;

 

When MySQL is executing the statement, it will treat only “dem” as the username and when it encounters another ‘o‘, then it treats ‘o‘ as a SQL command and it will throw and error message as it does not recognizes ‘o‘ as a valid SQL command.
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘o’ and password=”demopass”; ” at line 1
 

Awesome. The above error message states that there are some error in SQL syntax. If Magic Quotes were enabled, then we might not have got that above error message.
 

Now, let’s see what happens if the malicious user enters username as (‘ OR ‘1’=’1) and password as “demopass“.

The SQL query will look like this:

1
SELECT first_name, last_name, account_number FROM users_table WHERE username = '' OR '1'='1' AND password = 'demopass' ;

 

What does the above SQL query mean? It tells MySQL to find all rows with a username equal to “” (blank) or (1=1) and a password equal to “demopass“.
 

Let’s represent the above SQL query more logically to understand what’s actually wrong with the SQL statement:
 

username = “” OR “1=1” AND password = “demopass”
 

Now, we already know that 1=1 is always going to be TRUE. Hence, the user will be able to login to the system if the password is correct and the username is wrong.
 

Let’s analyze more worse scenario with a different attack string.
 

Attack String: ‘ OR 1=1–
 

Please note that ( and #) are used to terminate SQL statement. Let’s put the attack string in a SQL query and see how the final SQL statement will look.
 

Now, we will assume that the attacker does not even know the password.
 

1
SELECT first_name, last_name, account_number FROM users_table WHERE username = '' OR 1=1--' AND password = 'idonotknow' ;

 

See how the SQL statement is interpreted by MySQL.
 

username = “” OR 1=1– AND password = “idonotknow”
 

Please note in the above statement. As we know that is a statement terminator in SQL and 1=1 is always TRUE, after the statement terminator rest of the SQL query will not be executed.
 

Hence, ALL the records in the table will get returned and the user will be logged into the system. Attacker will be able to see the account number of the first record in the database.
 

Let’s help innocent MySQL
 

Magic Quotes

 

Magic Quotes is a controversial feature of the PHP scripting language, wherein special characters are prep-ended with a backslash before being passed on.
 

Magic Quotes were enabled by default in new installations of PHP3 and PHP4, and since their operation is behind the scenes and not immediately obvious, developers may be unaware of their existence and the potential problems that they can introduce.
 

We will have to turn on the Magic Quotes in php.ini file in the web-root of the website.
 

How Magic Quotes helps?

 

We need to be escape these quote characters ( both single and double quotes, as well as backslashes).
This is done by putting a slash in front of them.
 

So usename = dem’o becomes dem\’o , and MySQL can work out with that quotation mark as it is “protected” by the slash.
 

Let’s see what happens when Magic Quotes is turned on. Let’s see how the attacker is affected with Magic Quotes.
 

1
SELECT first_name, last_name, account_number FROM users_table WHERE username = '\' OR 1=1--' AND password = 'idonotknow' ;

 

The the username is interpreted as: \’ OR 1=1–

That’s a perfectly escaped SQL query, nothing can get injected. magic_quotes_gpc escapes the dangerous single quote for us. Hence, the attacker will not be able to log into the system.

Issues with Magic Quotes

 

Magic quotes only insert a backslash before a few characters, nothing more. This protects us from SQL injection only in some particular cases like above and only by co-incidence. If we have display_errors ON, we just made the attacker very happy with your database details outputted right before his eyes. Even if you don’t, there still is a possibility of blind SQL injection and using advance SQL injection methods it is possible to bypass magic_quotes_gpc function.
 
So, we need a way of escaping data that isn’t crappy, isn’t as prone to character set issues, and isn’t prone to magic breeding slashed quotes.
 
In November 2005 the core PHP developers decided on account of these problems that the magic quotes feature would be removed from PHP 6.
Once the Magic quotes features is withdrawn, then there’s going to be some major problems cropping up.
 

SQL Injection Mitigation
 

There are a number of ways to prevent MySQL injections within PHP. The most common ways are using functions such as addslashes() and mysql_real_escape_string().
 

addslashes()

 

addslashes() will return a string with a backslash before characters that need to be sanitized in database queries. These characters are single quotes (‘ = \’) double quotes (” = \”) and the nullbyte (%00 = \0).
 

addslashes() will only work if the query string is wrapped in quotes. A string such as the following would still be vulnerable to an SQL injection:
 

1
2
3
$user_name = addslashes( $_POST[ 'username' ] );
$password  = addslashes( $_POST[ 'password' ] );
$query = "SELECT first_name, last_name, account_number FROM users_table WHERE username = '$user_name' AND password = '$password' ";

mysql_real_escape_string()

 

mysql_real_escape_string() is a little bit more powerful than addslashes() as it calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.
 

As with addslashes(), mysql_real_escape_string() will only work if the query string is wrapped in quotes. A string such as the following would still be vulnerable to an SQL injection:
 

1
2
3
$user_name = mysql_real_escape_string( $_POST[ 'username' ] );
$password  = mysql_real_escape_string( $_POST[ 'password' ] );
$query = "SELECT first_name, last_name, account_number FROM users_table WHERE username = '$user_name' AND password = '$password' ";

 

sprintf()

 

sprintf() can be used with conversion specifications to ensure that the dynamic argument is treated the way it’s suppose to be treated. For example, if a call for the users ID number were in the string, %s would be used to ensure the argument is treated as an string. An example of this is as follows:
 

1
2
3
$user_name = $_POST[ 'username' ];
$password  = $_POST[ 'password' ];
$query = sprintf("SELECT first_name, last_name, account_number FROM users_table WHERE username = '%s' AND password = '%s' ", $user_name, $password);

 

htmlentities($var, ENT_QUOTES)

 

htmlentities() in conjunction with the optional second quote_style parameter, allows the use of ENT_QUOTES, which will convert both double and single quotes. This will work in the same sense as addslashes() and mysql_real_escape_string() in regards to quotation marks, however, instead of prepending a backslash, it will use the HTML entity of the quotation mark.
 

In addition to using ENT_QUOTES within htmlentities(), a third parameter can be set which forces the use of a character set within conversion. This will help stop unpredicted results from using multibyte characters in character sets such as BIG5 and GPK.
 

The following is an example of code which would help to prevent SQL injection in PHP.
 

1
2
3
4
5
$user_name = $_POST[ 'username' ];
$user_name = htmlentities( $user_name, ENT_QUOTES, 'UTF-8' );
$password  = $_POST[ 'password' ];
$password  = htmlentities( $password, ENT_QUOTES, 'UTF-8' );
$query = "SELECT first_name, last_name, account_number FROM users_table WHERE username = '$user_name' AND password = '$password' ";

 

Optimize SQL Code

 

Database servers are complex beasts and they have much more functionality than you need. As far as security is concerned, more is never better. For instance, the xp_cmdshell extended stored procedure in MS SQL gives access to the shell and this is just what a hacker dreams of. This is why you should disable this procedure and any other functionality, which can easily be misused. Just remove or at least disable any functionality you can do without.
 

Disable Default SQL Error Message

 

Error messages are useful to an attacker because they give additional information about the database and SQL queries. And all SQL attacks are usually based on type of error issued by SQL, it means that type of error decides the hackers approach for hacking the website or application. A better solution that does not compromise security would be to display a generic error message that simply states an error has occurred.
 

Store Database Credentials Securely

 

In order to minimize the damage in case of an SQL injection attack, always store database credentials in a separate encrypted file. Now even if a hacker manages to break in, he or she won’t benefit much as he cannot do much in your database.
 

Use Least Privilege Principle

 

The principle of least privilege is highly beneficial and it applies to SQL injections as well. Always think or check twice about what privileges you are providing to user or object. Suppose you wan to provide moderator access to some user, so only provide him the access of those tables which he/she need, rather than proving him the access of whole database. If you have to provide access to a system, its better to create partitioned table spaces inside database and provide access only to specific table space. This technique will drastically reduce the attack surface.
 

Disable Shells

 

Many databases offer shell access to the database which essentially is what an attacker or hacker needs. Because of this you need to close this open loophole. Every service provider has different method to disable the execution of shells on their database. So consult your Database documentation about how to disable shell access for your particular database or table space or particular table.
 

Use SQL Injection Tools To Check Vulnerabilities

 

Last but not the least, think like hacker. How a hacker can hack my database through SQL injection, what tools and techniques he can use to find the loopholes. You should always have a dry run of SQL injection hack tools like SQLi, Haviz, SQL injectme etc. More if you can afford retina vulnerability scanner then its too good. As it consists of all latest exposed vulnerabilities.

Thank you very much. I hope you all must have enjoyed. Please give comments so that we can do more better.

 
 

30,727 total views, 3 views today

The following two tabs change content below.

Ashfaq Ansari

Security Researcher
Ashfaq Ansari is the founder of HackSys Team code named "Panthera". He is a Security Researcher with experience in various aspects of Information Security. He has authored "HackSys Extreme Vulnerable Driver" and "Shellcode of Death". He has also written and published various whitepapers on low level software exploitation. His core interest lies in "Low Level Exploitation", "Reverse Engineering", "Program Analysis" and "Hybrid Fuzzing". He is a fanboy of Artificial Intelligence and Machine Learning. He is the chapter lead for null Pune.

Latest posts by Ashfaq Ansari (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *