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