Abstract

SQL Injection is an attack on the database via the addition of malicious code, mostly in the form of SQL queries, passed into a string that reaches past the application on to the database and causes it to execute against the database. In most cases the hacker is able to obtain unauthorized information off of the database.

This article is intended to discuss some of the ways that MySQL databases can be exposed via SQL Injection techniques.

Introduction

Simply put, the end goals of a SQL injection attack is to gain private (maybe confidential) data, perform a create or change on data which is not meant to be changed, or altogether purge the data, data object or dataset. Just a couple of months back, i.e. March 27th 2011 to be precise, Romanian Hackers by the name of “TinKode” and “Ne0h” attacked MySQL.com and Sun.com. They did this with a SQL injection attack, to gather table names, column names and email addresses stored in one of the tables. As this article states:http://www.eweek.com/c/a/Security/Oracles-Suncom-Hit-Along-with-MySQLCom-in-SQL-Injection-Attack-727118/ :

“TinKode has been busy in recent days going after MySQL databases.” It is worth noting that the article does not blame MySQL, the open-source DBMS, for the vulnerability but rather the Website code.

SQL Injection Examples

Say you have a table called “STUDENT_MARKS” which has columns – roll_number, last_name, first_name, mark, and your php code is to output the marks given the student rollnumber. Each student will know their own unique10 digit rollnumber. Your code looks something like this and has no check on the inputs given to the application or website:

// connection made against mysql database
// given input of say…
$rollnumber= “4’ or ‘1’=’1”;
// given query code of say… 
mysql_query(“SELECT * FROM STUDENT_MARKS WHERE rollnumber=’{$rollnumber}’”);

If someone had unauthorized access to query the “mysql” database within MySQL, which holds user specific information, then they could easily extract private data from such queries as: 4’ UNION SELECT GROUP_CONCAT(user) FROM mysql.user; Which would give them a comma-separated list of users.

// connection made against mysql database
// get some input from user
$bookname_val = $_POST["some-input"];
// insert value into BOOKS table
mysql_query("INSERT INTO BOOKS (bookname) VALUES ('" . $bookname_val . "')");

This is a similarly exploitable code with SQL injection, The above is exploitable because it is a dynamic SQL statement, which means that it is constructed by the concatenation of SQL with user-inputted values.

It can be a nightmare for an organization especially when a hack is such that it wipes off tables, database objects, databases, etc. and yes even this is possible via SQL Injection:

// connection made against mysql database
// given input of say…
$shipped_to_last_name= “'; DELETE FROM DELIVERY';”;
// given query code of say… 
mysql_query(“SELECT * FROM DELIVERY WHERE shipper_to=’{$shipped_to_last_name}’”);

There are books that claim that stacking of queries is not allowed in MySQL, unlike Postgres, but we have not found this to be true. If you do happen to stack them it will work in the tests unless you use the proper PHP functions to force them to be avoided.

1. Unwanted Read
2. Unwanted Write
3. Unwanted Deletion/Truncation

Cheat Sheet On SQL Injection Attacks Against MySQL Databases

In the tables below, Clarke summarizes the various SQL injection attack techniques; a great majority worked in MySQL 5.0, and MySQL 5.1:

Table 10.11: Extracting MySQL Server Configuration Information

SQL Injection Attacks and Defense By Justin Clarke, Copyright Elsevier, Inc. © 2009, Publisher: Elsevier Science and Technology Books, Inc.

Table 10.12: Extracting Schema Information from MySQL 5.0 and Later

SQL Injection Attacks and Defense By Justin Clarke, Copyright Elsevier, Inc. © 2009, Publisher: Elsevier Science and Technology Books, Inc.

Table 10.13: Blind SQL Injection Functions

SQL Injection Attacks and Defense By Justin Clarke, Copyright Elsevier, Inc. © 2009, Publisher: Elsevier Science and Technology Books, Inc.

SQL Injection by Exploiting the INFORMATION_SCHEMA

INFORMATION_SCHEMA database schema is basically the entity in MySQL that holds all of the data dictionary views. So it contains views that hold database metadata information. This discussion on INFORMATION_SCHEMA is in greater depth as a majority of the exploits with SQL injection are, in fact, completed off of the views and tables within this schema. As with other DBMS’, this schema and more specifically the views under it can easily be a gold-mine for those hackers that seek to find information about other databases and users on a shared server. For example, as is the case in a shared Cloud Computing environment. Fortunately, Rackspace Cloud Sites has been ahead of the game and on top of installing security patches that avoid the mentioned hack below.

Suppose a mysql “root” user creates a user called “NEWUSER” that has rights to connect to a MySQL Database Server from any host: 

Note: The database “SQL_INJ1” does not have to pre-exist for the user to be created in MySQL.

In MySQL version 5.0.77 that comes with Redhat RHEL 5 distributions, this user can login and then see the following two databases by default:

Now, in a secure mysql installation, you will not see the “test” database seen above under “Show databases”. Rackspace Cloud Sites makes sure of this. But regardless, a newly granted or created user will always be able to query against tables in “information_schema”. The contents visible to a logged in, (and non-root or non-admin), user as far as the “information_schema” goes are only those data that a unique and relevant to the user that is logged in. So, say user “NEWUSER” created a database by the name “SQL_INJ1” and a table called “tab1”.

Let’s say this user wanted to view all of the columns under, he could query the “COLUMNS” table under “information_schema” but the only visible information to this user would be his own two columns created under “tab1” table and no other columns for any other tables.

There is one very dangerous bug in MySQL that can be exploited by all users on a shared MySQL server. That bug has to do with the INNODB_BUFFER_POOL_CONTENT table which contains all of the information on what is cached in the MySQL buffer pool including column names, table names, sizes, etc. for ALL users and databases on that host system! So if a user in MySQL 5.0 did make use of a SQL to extract this information, it could be done even outside of a SQL injection attack really. But if this were a code via a malicious hacker, then they could easily conduct this attack via a 3rd party site. Here is more on what you get to see in INNODB_BUFFER_POOL_CONTENT.:

The effect of this attack on a system that has several databases and users and whose buffer pool cache is large is very detrimental! There can be three folds of risks here:

• DOS (Denial of Service) as this is a very slow and un-indexed query and can cause a system to start swapping if several of these queries are issued concurrently
• Performance Degradation with issuance on a large buffer pool cache
• Leak of this and other users private data

To demonstrate the security risk of this query “SELECT * FROM `INNODB_BUFFER_POOL_CONTENT`”, we did a reproduction of such a hack scenario on a test box and successfully mimicked the behavior seen below via a few users. The output below is that from “show processlist” which shows the concurrent sessions and threads running along with the statements, the status of the statement run, time that statement has been running and users who run them.

Id: 2223890
   User:  illssr
     db: information_schema
Command: Query
   Time: 1015
  State: Writing to net
   Info: SELECT * FROM `INNODB_BUFFER_POOL_CONTENT`
*************************** 4. row ***************************
     Id: 2223891
   User: ncUsr
    db: information_schema
Command: Query
   Time: 1016
  State: Writing to net
   Info: SELECT * FROM `INNODB_BUFFER_POOL_CONTENT`

The problem still exists in the RHEL 5 Linux distro version of MySQL 5.0. But, this has also been resolved in MySQL 5.0 by applying a third party patch by Percona on this security risk: https://code.launchpad.net/~percona-dev/percona-patches/5.0.84-fix-bug-352840 Rackspace Cloud Sites has identified this as a security bug that we have taken care by including this Percona security patch. Basically what this patch does is remove select on INNODB_BUFFER_POOL_CONTENT from unprivileged users.

NOTE: This issue has also been resolved in later versions of MySQL as I’ve tested below in MySQL 5.5 and here is a verification of that:

Note: Below no INNODB_BUFFER_POOL_CONTENT or equivalent data dictionary view in MySQL 5.5:

Preventing MySQL SQL Injections

An example of using pre-existing php function to perform a sanity check on the data entered by users is:mysql_real_escape_string. This escapes special characters in a string for use in an SQL statement. mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

Here is another example of SQL Injection attack:

<?php
// User input value unchecked
$_POST['userid'] = '';
$_POST['pswd'] = "' UNION SELECT TABLE_NAME from information_schema.tables where column_name LIKE ’%SSN%’";
 
// Query  
$query = "SELECT * FROM CREDIT_CARD_INFO WHERE userid='{$_POST['userid']}' AND password='{$_POST['pswd']}'";
mysql_query($query);
 
// Query read by MySQL DBMS:
echo $query;
?> 

The end query that will be run against the database will be:

 
SELECT * FROM CREDIT_CARD_INFO WHERE userid='' AND password='' UNION SELECT TABLE_NAME FROM information_schema.tables where column_name LIKE ’%SSN%’

This will give out all the table names to the hacker that has the “SSN” column in it, which the hacker then use to perform subsequent hacks in those tables.

The way to resolve this vulnerability using mysql_read_escape_string would be:

$query = sprintf("SELECT * from CREDIT_CARD_INFO where userid='%s' AND pswd='%s'",
            mysql_real_escape_string($userid),
            mysql_real_escape_string($pswd));

One example of a weak, database design would be one that is missing constraints – foreign key or check constraints. Say I am a hacker that has the bits of the puzzle via SQL injection or other types of hacks to know that I have two tables “EMPLOYEES” and “DEPT” and then I have access to inserting into EMPLOYEES table, suppose I tried to insert all new rows into EMPLOYEES. Now, if my database design was strong enough to not allow nulls for most of the rows (where applicable) and to have a foreign key constraint on DEPT_ID column in EMPLOYEES table going back to DEPT_ID primary key on DEPT table, then the chances that the hacker can quickly just insert some jibberish into my EMPLOYEE table is lower.

Another design flaw can be when database records on a numeric primary key value start off with a 1. Such a number can easily be guessed or used for exploitation in SQL Injections. As a common practice, DBAs should avoid starting off PK numeric/int values at 1.

The DBMS thrown error or warning must ideally never be shared with the end user of an application or a website. The true MySQL error code from the DBMS may give up a lot of information about the database design (table or column structures), application or website design. Avoiding sharing the DBMS error/warning is precisely to help avoid giving up too much information to the malicious attacker who could exploit it to perform the completion of his attack. A cryptic or hidden MySQL error message that is translated from its original form should be displayed to the end user.

The most common way to handle SQL failures is as this article mentions on page 8 see footnote for reference:

function sql_failure_handler($query, $error) {
$msg = htmlspecialchars(“Failed Query: {$query}<br>SQL Error: {$error}”);
error_log($msg, 3, “/home/site/logs/sql_error_log”);
if (defined(‘debug’)) {
return $msg;
}
return “Requested page is temporarily unavailable, please try again later.”;
}
mysql_query($query) or die(sql_failure_handler($query, mysql_error()));

The Least Privilege for Database Accounts specially come into play on Cloud Computing or hosting provider sites where the same server hosts multiple sites or databases or both. If someone compromises one account or finds an SQL injection hole on one site, the attacker may then be able to access all of the data against other databases as well. Defense in Depth would then be violated. Creating separate, and limited accounts for each database is important in both shared and unshared hosting environments but it specially begs for attention owing to the exploits that SQL Injection can yield.

MySQL, Ubuntu, Redhat, etc. all come out with their OS and/or MySQL security patches that are specific to resolving potential security threats such as SQL injection. Example: https://bugzilla.redhat.com/show_bug.cgi?id=640808https://bugzilla.redhat.com/buglist.cgi?component=vulnerability&product=Security%20Response

Prepared queries, also called prepared statements, solve a great many of the aforementioned risks. Prepared queries are query “templates”: the structure of the query is pre-defined and fixed and includes placeholders that stand-in for real data. The placeholders are typically type-specific—for example, int for integer data and text for strings—which allows the database to interpret the data strictly.

In PHP for MySQL database this is how we would do a selective DELETE on USERS table based on specific usernames:

$Value = $_POST["user-name"];
$preparedStatement = $db->prepare('DELETE FROM USERS where username IN (:username)');
$preparedStatement->execute(array(':username' => $Value));

Basically the MySQL database server independent of any parameters parses Prepared SQL statements.

When you pass the SQL Statement to prepare it is then parsed and compiled by the database server. Using either parameter markers signified by “?” or a named parameter such as like :username, you are asking the DBMS engine what to filter on. With execute, the prepared statement is added with the parameter values you specify. Note here that the parameter values are added with the compiled statement, not with a SQL string. For example, suppose that the $username variable has ‘John’; DELETE * FROM USERS the result would simply be a search for the string “‘John’; DELETE * FROM USERS” and there will be no table truncation or deletion! We know that SQL injection conducts itself by fooling the script into including malicious text when it creates SQL to push to the database. By separately sending the SQL statement from the parameters, we limit or eliminate the vulnerability of that unknown factor. Prepared Statements have the added benefit of having higher performance as it is only compiled and parsed once per session.

Summary

This article summarizes the various MySQL SQL Injection attacks, conducts various tests using the LAMP, stack namely only Linux, PHP and MySQL needed for this particular test although Apache’s configuration was also tested to see the additional benefit it has in preventing specific other kinds of SQL injection attacks. It is a more infrastructure level documentation although a fair bit of coding and SQL is also shown in this. Using codes and techniques in this documentation, many applications can easily be proven to be exploitable via SQL Injection attacks. Reading this document will give a better insight into the ways that SQL injection attacks are performed while a MySQL Database backend is in play. It also discusses some best practices and gives suggestions on how to prevent SQL Injection as best as possible.

References

http://php.net/manual/en/function.mysql-real-escape-string.php

http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf

http://pentestmonkey.net/blog/mysql-sql-injection-cheat-sheet/

http://www.eweek.com/c/a/Security/Oracles-Suncom-Hit-Along-with-MySQLCom-in-SQL-Injection-Attack-727118/

http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php

Chapter 12 – PHP and MySQL: Create-Modify-Reuse by Tim Boronczyk and Martin E. Psinas

Chapter 2 – Security Principles and Vulnerabilities outside Drupal – Cracking Drupal: A Drop in the Bucket by Greg Knaddison

Chapter 10 – References – SQL Injection Attacks and Defense by Justin Clarke

 http://php.net/manual/en/function.mysql-real-escape-string.php 
 http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf 

1. Add logic check in the php or other code such that all user inputs are first verified either by programmer built-in logic into the application or using pre-existing php functions to solidify the logic check.
2. Use strong database design techniques
3. Proper Error handling mechanisms in place in the application code
4. Defense in Depth by applying LPA rule
5. Keep the login configuration PHP files safe and hidden from others
6. Use Prepared Statements

Views: 26