SEED Labs – SQL Injection Attack Lab 8
in the cache, the parsing, compilation and query optimization phases will be skipped. The compiled query
is then passed to the execution phase where it is actually executed.
Prepared statement comes into the picture after the compilation but before the execution step. A pre-
pared statement will go through the compilation step, and be turned into a pre-compiled query with empty
placeholders for data. To run this pre-compiled query, data need to be provided, but these data will not go
through the compilation step; instead, they are plugged directly into the pre-compiled query, and are sent
to the execution engine. Therefore, even if there is SQL code inside the data, without going through the
compilation step, the code will be simply treated as part of data, without any special meaning. This is how
prepared statement prevents SQL injection attacks.
Here is an example of how to write a prepared statement in PHP. We use a SELECT statement in the
following example. We show how to use prepared statement to rewrite the code that is vulnerable to SQL
injection attacks.
$sql = "SELECT name, local, gender
FROM USER_TABLE
WHERE id = $id AND password =’$pwd’ ";
$result = $conn->query($sql)
The above code is vulnerable to SQL injection attacks. It can be rewritten to the following
$stmt = $conn->prepare("SELECT name, local, gender
FROM USER_TABLE
WHERE id = ? and password = ? ");
// Bind parameters to the query
$stmt->bind_param("is", $id, $pwd);
$stmt->execute();
$stmt->bind_result($bind_name, $bind_local, $bind_gender);
$stmt->fetch();
Using the prepared statement mechanism, we divide the process of sending a SQL statement to the
database into two steps. The first step is to only send the code part, i.e., a SQL statement without the actual
the data. This is the prepare step. As we can see from the above code snippet, the actual data are replaced
by question marks (?). 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. It binds the data to the
corresponding question marks of the prepared statement. In the bind param() method, the first argument
"is" indicates the types of the parameters: "i" means that the data in $id has the integer type, and "s"
means that the data in $pwd has the string type.
Task. In this task, we will use the prepared statement mechanism to fix the SQL injection vulnerabilities.
For the sake of simplicity, we created a simplified program inside the defense folder. We will make
changes to the files in this folder. If you point your browser to the following URL, you will see a page
similar to the login page of the web application. This page allows you to query an employee’s information,
but you need to provide the correct user name and password.
URL: http://www.seed-server.com/defense/
The data typed in this page will be sent to the server program getinfo.php, which invokes a program
called unsafe.php. The SQL query inside this PHP program is vulnerable to SQL injection attacks. Your
job is modify the SQL query in unsafe.php using the prepared statement, so the program can defeat SQL
injection attacks. Inside the lab setup folder, the unsafe.php program is in the image_www/Code/
defense folder. You can directly modify the program there. After you are done, you need to rebuild and