CIS 458 Lab 5: SQL Injection Lab

This lab assignment may be done alone, or in groups of two.

Objective: The objective of this lab is for students to gain a better understanding of SQL injection attacks and defences.

Grading: Please turn in answers to the numbered questions below. Each question is worth points as specified.

This lab has been modified by Andrew Kalafut from the original "SQL Injection Attack Lab" written by Wenliang Du of Syracuse University. Copyright 2015 Andrew Kalafut. Copyright 2006 - 2011 Wenliang Du, Syracuse University. The development of the original document is/was funded by three grants from the US National Science Foundation: Awards No. 0231122 and 0618680 from TUES/CCLI and Award No. 1017771 from Trustworthy Computing. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation. A copyof the license can be found at


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 SQL queries are not carefully constructed, SQL-injection vulnerabilities can occur. SQL-injection attacks are one of the most frequent attacks on web applications.

In this lab, we modified a web application called Collabtive, and disabled several countermeasures implemented by Collabtive. As the results, we created a version of Collabtive that is vulnerable to the SQL-Injection attack. Although our modifications are artificial, they capture the common mistakes made by many web developers. The 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 Setup

This lab should be done on the SEED project VMWare images available on the EOS computers. Please do all steps in the SEED VM.

In this lab, we will be attacking a web application called Collabative, which is accessable from the url within the SEED VM only.

The version of PHP on the SEED VM provides a mechanism to automatically defend against SQL injection attacks. The method, called magic quote, automatically escapes all single and double quote characters in all user input. However, it has some downsides, such as high overhead. This method does not exist in current versions of PHP and is even deprecated in the installed version. Please turn off this countermeasure. You can do so by editing /etc/php5/apache2/php.ini. Find the line magic_quotes_gpc = On and change the On to Off. After doing so, you will have to restart apache, by running sudo service apache2 restart.

Task 1: SQL Injection Attack on SELECT Statements

In this task, you need to manage to log into Collabtive at, without 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.

The authentication is implemented by include/class.user.php in the Collabtive root directory (/var/www/SQL/Collabtive/). It uses the user-provided data to find out whether they match with the name and pass 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  user
     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 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.

There is a SQL-injection vulnerability in the above query.

  1. (1 point) How can you log into another persons account without knowing the correct password? (What input is needed into the username and password text boxes).
  2. (2 points) Explain why the input you gave in response to the previous question works. What specifically is your input doing?

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 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. Please find the vulnerability, and then use it to 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.

  1. (1 point) What input did you give in the profile update screen for a successful attack
  2. (2 points) How does this attack work? How did you come up with the input you used?

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 including magic_quotes_gpg, previously mentioned above.

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. For example, the following:

$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);

//The following two functions are only useful for SELECT statements
$stmt->bind_result($bind_ID, $bind_name, $bind_locale, $bind_lastlogin);

Parameters for the 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.

  1. (4 points)Please use the prepare statement mechanism to fix the SQL injection vulnerability (both vulnerabilities you exploited) in the Collabtive code. For this question, turn in a printout of the class.user.php file with your fixes implemented.