Alexa metrics
Live Chat

Welcome to UKFast, do you have a question? Our hosting experts have the answers.

Chat Now
Sarah UKFast | Account Manager

The Potency of SQL Injection – A Technical Perspective

16 August 2010 by Pingu

SQL Piechart

Break down of attack types

Most web developers know that they should sanitize their web input. However recent figures from the UK Security Breach Investigations Report 2010 indicate that 40 per cent of all website attacks are due to SQL injections.

SQL injection attacks allow perpetrators to leak data, usually by making a web application perform a query it wasn’t intended to do. However, what most fail to realize is under the right conditions SQL injection attacks can be much more potent than data exposure (which is a serious breach in itself). A well crafted attack has the potential to subvert your entire system where circumstances allow.

To begin, let’s discuss what the SQL injection attack is, and how it works.

A Basic Example

We shall take a PHP MySQL query and consider the problem with it.

mysql_query("SELECT id,username,password FROM user_table \
 WHERE username="'.$_GET['username']."');

So when a user executes a query genuinely, the variable will typically be replaced and the query such, I.E:

mysql_query("SELECT id,username,password FROM user_table WHERE username='matthew'");

The problem arises however when the data input contains characters which are meaningful in an SQL statement. Consider for example logging in with the username ma’tthew (note the intentional quotes in the middle of the username). When we do the variable expansion the query ends up appearing as:

mysql_query("SELECT id,username,password FROM user_table WHERE username='ma'tthew";

When you run this, the query is invalid SQL because the entire statement is syntactically incorrect. What has happened is the attacker has altered the behaviour of the SQL statement – actually gaining control of it. This allows the attacker to continue the statement altering to fetch data that is normally not permitted by the original statement.

This kind of attack is well known by web developers. Unfortunately for system administrators and web developers alike the problem doesn’t stop here. If the privileges that have been set by the system/database administrator are too lax it’s possible to reap data right off the disk and worse still, deploy arbitrary data onto the disk.

The Worst Case Scenario

Lets analyze the worst possible situation demonstrating this. A lax web developer has written a very simple table described below. To save time and effort he’s simply used the admin’s (root) user details in this webapp, along with all other webapps on the server.
mysql> desc data;
| Field | Type        | Null | Key | Default | Extra          |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| info  | varchar(32) | YES  |     | Nothing |                |
2 rows in set (0.00 sec)

The webpage used is PHP written as follows:

mysql_connect('localhost','root','xxxxxx') or die(mysql_error());
mysql_select_db('mywebapp') or die(mysql_error());

echo "<table>\n";
echo "<tr><td>ID</td><td>Info</td></tr>\n";

if (isset($_GET['search'])) {
   $r = mysql_query("SELECT * from data where info like '".$_GET['search']."'") \
      or die(mysql_error());
   echo "SELECT * from data where info like '".$_GET['search']."'";
else {
   $r = mysql_query("SELECT * from data") or die(mysql_error());

while ($row = mysql_fetch_array($r, MYSQL_NUM)) {
   echo "<tr><td>$row[0]</td><td>$row[1]</td></tr>\n";

echo "</table>";

<form name="test">
Search: <input type=text name=search value=""><br/>
<input type="submit"/>

However, the developer also has also run “chmod 777” on a directory called “images” which is used for another part of the website. This is a common work-around used to avoid permission problems when creating files, by allowing anyone to create files.

The SQL injection vulnerability occurs on line 9. Because the input is not sanitized, the user can perform a fake search and take control of the SQL. The attacker, having already tried standard SQL injection techniques has seen little data of interest remains on the databases. Rather than find the other databases, the attacker wants to spawn a shell. But, can this be done from within mysql?

The answer is, yes of course it can. This is because the db user has the FILE privilege set that means he can read files in and write files out. The attacker needs to know where the document root is for the website. It’s not outright retrievable from SQL but it is readable in the httpd.conf.

SQL inject readBy ut