Lab 11 - SQL Injection Attacks

Introduction

SQL injection is a code injection technique that exploits the vulnerabilities in the interface between web applications and database servers. The vulnerability is present when user's inputs are not correctly checked within the web applications before sending to the back-end database servers.

Many web applications take inputs from users, and then use these inputs to construct SQL queries, so the web applications can pull the information out of the database. Web applications also use SQL queries to store information in the database. These are common practices in the development of web applications. When the SQL queries are not carefully constructed, SQL-injection vulnerabilities can occur. SQL-injection attacks is one of the most frequent attacks on web applications.

Students' goal in this lab is to find ways to exploit the SQL-Injection vulnerabilities, demonstrate the damage that can be achieved by the attacks, and master the techniques that can help defend against such attacks.

Lab Environment

For this lab you will work in groups of two and turn in one common report.

For this lab you need to use the provided SEEDUbuntu12.4 virtual machine image, the same as for labs 5 and 8. Follow the instructor's instructions to start and configure the VM.

Login Name: seed

Password: dees

Lab Set-up - Just read for your information, then execute the "Turn off the Countermeasure" part

In this lab, we need three things, all of which are already installed in the provided VM image: (1) the Firefox web browser, (2) the Apache web server, and (3) the Collabtive project management web application. For the browser, we need to use the LiveHTTPHeaders extension for Firefox to inspect the HTTP requests and responses. The pre-built Ubuntu VM image provided to you has already installed the Firefox web browser with the required extensions.

Starting the Apache Server. The Apache web server is included in the pre-built Ubuntu image. The web server is started by default. If you need to start the web server, use the following command:

sudo service apache2 start

The Collabtive Web Application. We use an open-source web application called Collabtive in this lab. Collabtive is a web-based project management system. This web application is already set up in the pre-built Ubuntu VM image. We have also created several user accounts on the Collabtive server. To see all the users' account information, first log in as the admin using the following password; other users' account information can be obtained from the post on the front page or the “Manage users” tab.

username: admin

password: admin

Note: all user passwords are the same as username ex(user: ted; password: ted).
User emails are user@syr.edu. Ex (ted@syr.edu)

Configuring DNS. We have configured the following URL needed for this lab. To access the URL, the Apache server needs to be started first:

URL

Description

Directory

http://www.sqllabcollabtive.com

Collabtive

/var/www/SQL/Collabtive

The above URL is only accessible from inside of the virtual machine, because we have modified the /etc/hosts file to map the domain name of each URL to the virtual machine's local IP address (127.0.0.1). You may map any domain name to a particular IP address using /etc/hosts. For example you can map http://www.example.com to the local IP address by appending the following entry to /etc/hosts:

127.0.0.1 www.example.com

If your web server and browser are running on two different machines, you need to modify /etc/hosts on the browser's machine accordingly to map these domain names to the web server's IP address, not to 127.0.0.1.

Configuring Apache Server. In the pre-built VM image, we use Apache server to host all the web sites used in the lab. The name-based virtual hosting feature in Apache could be used to host several web sites (or URLs) on the same machine. A configuration file named default in the directory "/etc/apache2/sites-available" contains the necessary directives for the configuration:

1. The directive "NameVirtualHost*" instructs the web server to use all IP addresses in the machine (some machines may have multiple IP addresses).

2. Each web site has a VirtualHost block that specifies the URL for the web site and directory in the file system that contains the sources for the web site. For example, to configure a web site with URL http://www.example1.com with sources in directory /var/www/Example_1/, and to configure a web site with URL http://www.example2.com with sources in directory /var/www/Example_2/, we use the following blocks:

<VirtualHost *>

ServerName http://www.example1.com

DocumentRoot /var/www/Example_1/

</VirtualHost>

<VirtualHost *>

ServerName http://www.example2.com

DocumentRoot /var/www/Example_2/

</VirtualHost>

You may modify the web application by accessing the source in the mentioned directories. For example, with the above configuration, the web application http://www.example1.com can be changed by modifying the sources in the directory /var/www/Example_1/.

Turn Off the Countermeasure

PHP provides a mechanism to automatically defend against SQL injection attacks. The method is called magic quote, and more details will be introduced in Task 3. Let us turn off this protection first (this protection method is deprecated after PHP version 5.3.0).

  1. In the terminal, type sudo gedit /etc/php5/apache2/php.ini
  2. Find the line: magic_quotes_gpc = On
  3. Change it to this: magic_quotes_gpc = Off
  4. Save the changes.
  5. Restart the Apache server by running sudo service apache2 restart.

Lab Tasks

Submission

You will work in groups of two and turn in one lab report

You must create a folder on your public_html called "Lab11" (without the quotes) and store your work in that directory. Because you will do this lab in teams, only one submission is required for each team. However, the final report should be stored in both of the team members Lab11 folder. Create a file lastname1_lastname2_lab11.docx. For this lab you need to submit a detailed lab report (in lastname1_lastname2_lab11.docx) to describe what you have done and what you have observed. Please provide details using screenshots. You also need to provide explanation to the observations that are interesting or surprising. Complete this report as you go through the tasks below!!

Task 1: SQL Injection Attack on SELECT Statements

In this task, you need to manage to log into Collabtive at www.sqllabcollabtive.com, with-out providing a password. You can achieve this using SQL injections. Normally, before users start using Collabtive, they need to login using their user names and passwords. Collabtive displays a login window to users and ask them to input username and password. The login window is displayed in the following:
login page

The authentication is implemented by include/class.user.php in the Collabtive root directory (i.e., /var/www/SQL/Collabtive/). It uses the user-provided data to find out whether they match with the username and user password fields of any record in the database. If there is a match, it means the user has provided a correct username and password combination, and should be allowed to login. Like most web applications, PHP programs interact with their back-end databases using the standard SQL language. In Collabtive, the following SQL query is constructed in class.user.php to authenticate users:

$sel1 = mysql_query ("SELECT ID, name, locale, lastlogin, gender FROM USERS_TABLE WHERE (name = '$user' OR email = '$user') AND pass = '$pass'");
$chk = mysql_fetch_array($sel1);
if (found one record)
then {allow the user to login}

In the above SQL statement, the USERS_TABLE is a macro in PHP, and will be replaced by the users table named user. The variable $user holds the string typed in the "Username" textbox, and $pass holds the string typed in the "Password" textbox. User's inputs in these two textboxs are placed directly in the SQL query string.

SQL Injection Attacks on Login: There is a SQL-injection vulnerability in the above query. Can you take advantage of this vulnerability to achieve the following objectives?

  • Task 1.1: Can you log into another person's account without knowing the correct password? Support your conclusions with observations with screenshots. Refer to the 'Handy Info->Printing out debugging information' section below to generate screenshot evidence.
  • Task 1.2: Can you find a way to modify the database (still using the above SQL query)? For example, can you add a new account to the database, or delete an existing user account? Obviously, the above SQL statement is a query-only statement, and cannot update the database. However, using SQL injection, you can turn the above statement into two statements, with the second one being the update statement. Please try this method, and see whether you can successfully update the database. Describe your conclusions with observations with screenshots.

To be honest, we are unable to achieve the update goal. This is because of a particular defense mechanism implemented in MySQL. In the report, you should show us what you have tried in order to modify the database. You should find out why the attack fails, what mechanism in MySQL has prevented such an attack. You may look up evidences (second-hand) from the Internet to support your conclusion. However, a first-hand evidence will get more points (use your own creativity to find out first-hand evidences). If you do find ways to succeed in the attacks, you will be awarded bonus points.

Task 2: SQL Injection on UPDATE Statements

In this task, you need to make an unauthorized modification to the database. Your goal is to modify another user's profile using SQL injections. In Collabtive, if users want to update their profiles, they can go to "My account", click the "Edit" link, and then fill out a form to update the profile information. After the user sends the update request to the server, an UPDATE SQL statement will be constructed in edit method in include/class.user.php. The objective of this statement is to modify the current user's profile information in the users table. There is a SQL injection vulnerability in this SQL statement. Find the vulnerability (look in the code), and then use it to do the following:

Change another user's profile without knowing his/her password. For example, if you are logged in as Alice, your goal is to use the vulnerability to modify Ted's profile information, including Ted's password. After the attack, you should be able to log into Ted's account. Note that in Collabtive, passwords are hashed, by using the sha1 function. There are several online sha1 generators. You can use one to compute the hash of the new password you want to inject for the attacked user (Ted).

Describe your actions, and observations with screenshots. Refer to the 'Handy Info->Printing out debugging information' section below to generate screenshot evidence.

Task 3: Countermeasures

The fundamental problem of SQL injection vulnerability is the failure of separating code from data. When constructing a SQL statement, the program (e.g. PHP program) knows what part is data and what part is code. Unfortunately, when the SQL statement is sent to the database, the boundary has disappeared; the boundaries that the SQL interpreter sees may be different from the original boundaries, if code are injected into the data field. To solve this problem, it is important to ensure that the view of the boundaries are consistent in the server-side code and in the database. There are various ways to achieve this objective.

  • Task 3.1: Escaping Special Characters using magic_quotes_gpc. In the PHP code, if a data variable is the string type, it needs to be enclosed within a pair of single quote symbols ('). For example, in the SQL query listed above, we see name = '$user'. The single quote symbol surrounding $user basically "tries" to separate the data in the $user variable from the code. Unfortunately, this separation will fail if the contents of $user include any single quote. Therefore, we need a mechanism to tell the database that a single quote in $user should be treated as part of the data, not as a special character in SQL. All we need to do is to "escape" the quote by adding a backslash (\) before the single quote.

    PHP provides a mechanism to automatically add a backslash before single-quote ('), double quote ("), backslash (\), and NULL characters. If this option is turned on, all these characters in the inputs from the users will be automatically escaped. To turn on this option, go to /etc/php5/apache2/php.ini, and add magic_quotes_gpc = On (the option is already on in the VM provided to you). Remember, if you update php.ini, you need to restart the Apache server by running sudo service apache2 restart otherwise, your change will not take effect.

    Turn on/off the magic quote mechanism, and see how it helps the protection. Repeat task 1.1. Describe your observations with screenshots.

    Please note that starting from PHP 5.3.0 (the version in our provided VM is 5.3.5), the feature has been DEPRECATED, due to several reasons:

    • Portability: Assuming it to be on, or off, affects portability. Most code has to use a function called get_magic_quotes_gpc() to check for this, and code accordingly.
    • Performance and Inconvenience: not all user inputs are used for SQL queries, so mandatory escaping all data not only affects performance, but also become annoying when some data are not supposed to be escaped.

  • Task 3.2: Escaping Special Characters using mysql_real_escape_string. A better way to escape data to defend against SQL injection is to use database specific escaping mechanisms, instead of relying upon features like magical quotes. MySQL provides an escaping mechanism, called mysql_real_escape_string(), which prepends backslashes to a few special characters, including \x00, \n, \r, \, ', " and \x1A. Please use this function to fix the SQL injection vulnerabilities identified in the previous tasks. You should disable the other protection schemes described in the previous tasks before working on this task.
    Describe your observations with screenshots. How is this output different than the output in Task 3.1? Refer to the 'Handy Info->Printing out debugging information' section below to generate screenshot evidence.
  • Task 3.3: (Extra credit) Prepare Statement. A more general solution to separating data from SQL logic is to tell the database exactly which part is the data part and which part is the logic part. MySQL provides the prepare statement mechanism for this purpose.
    $db = new mysqli("localhost", "user", "pass", "db");
    $stmt = $db->prepare("SELECT ID, name, locale, lastlogin FROM users WHERE name=? AND age=?");
    $stmt->bind_param("si", $user, $age);
    $stmt->execute();
    //The following two functions are only useful for SELECT statements
    $stmt->bind_result($bind_ID, $bind_name, $bind_locale, $bind_lastlogin);
    $chk=$stmt->fetch();

    Parameters for the new mysqli() function can be found in /config/standard/config.php . Using the prepare statement mechanism, we divide the process of sending a SQL statement to the database into two steps. The first step is to send the code, i.e., the SQL statement without the data that need to be plugged in later. This is the prepare step. After this step, we then send the data to the database using bind_param(). The database will treat everything sent in this step only as data, not as code anymore. If it's a SELECT statement, we need to bind variables to a prepared statement for result storage, and then fetch the results into the bound variables.
    Please use the prepare statement mechanism to fix the SQL injection vulnerability in the Collabtive code. In the bind_param function, the first argument "si" means that the first parameter ($user) has a string type, and the second parameter ($age) has an integer type.
    Describe your observations with screenshots.

Handy Info

Print out debugging information.
When we debug traditional programs (e.g. C programs) without using any debugging tool, we often use printf() to print out some debugging information. In web applications, whatever is printed out by the server-side program is actually displayed in the web page sent to the users; the debugging printout may mess up with the web page. There are several ways to solve this problem. A simple way is to print out all the information to a file. For example, the following code snippet can be used by the server-side PHP program to print out the value of a variable to a file.

$Data = "Enter SQL query here";
$myFile = "/tmp/mylog.txt";
$fh = fopen($myFile, 'a+') or die("can't open file");
fwrite($fh, $Data."\n");
fclose($fh);

Deliverables

  1. You and your teammate need to submit a file lastname1_lastname2_Lab11.docx describing what you have done and what you have observed. Please provide details using screen shots. You also need to provide explanation to the observations that are interesting or surprising. Only one report is required for each team.
    Your report will be organized as follows:
    Task 1: SQL Injection Attack on SELECT Statement
    Task 1.1: Can you log into another person's account without knowing the correct password?
    -Observations and screenshots
    Task 1.2: Can you find a way to modify the database (still using the above SQL query)?
    -Observations and screenshots
    Task 2: SQL Injection on UPDATE Statements
    -Observations and screenshots
    etc.
  2. The report should be in a folder called "Lab11" (without the quotes) on both your public_html folders (you and your teammate). Your instructor will assume that your web pages are viewable at http://mope.academy.usna.edu/~mXXXXXX/Lab11/lastname1_lastname2_lab11.docx where XXXXXX is your alpha number.
  3. In your default.html page on your public_html, add a link to lastname1_lastname2_lab11.docx file under the heading "Lab11"
  4. Turn in (due before lab on Friday) One submission per team:
    1. Paper submission: turn in the following hardcopy at the beginning of class on the due date, stapled together in the following order (coversheet on top):
      1. A completed assignment coversheet. Your comments will help us improve the course.
      2. A printout of lastname1_lastname2_lab11.docx with your answers and screenshots. You can copy 2 or 4 pages per sheet to conserve paper and ink.
    2. Electronic submission: upload the lastname1_lastname2_lab11.docx file to Lab11 assignment on Blackboard.

Acknowledgements

This lab was adapted from the SQL Injection lab created by Wenliang Du at Syracuse University