Project SQL-X4 for CNIT 123: Fixing MySQL with Parameterized Queries (15 pts.)

Requirements

You will need a Linux machine with SQLol on it, including the simple page, which you prepared in previous projects. Kali Linux 2 is fine.

Purpose

Input validation provides some protection against SQL injection by escaping or removing quotes and some other special characters, but that's not an infallible defense. Some combinations of characters are interpreted as special characters, or translated into special characters after the filtering. And, as you saw in the first project, some fields, including numerical fields, allow SQL injection without any special characters.

Parameterized Queries provide better defense.

Testing the "Find Users" Page

In your Linux machine, in Firefox, go to

localhost/sqlol/search.htm

A "Find Users" page opens, as shown below:

In the Name field, type

O'Neil
Click the Submit button.

You should see an error message, as shown below:

Parameterized Queries

In a Terminal window, execute these commands:

cd /var/www/html/sqlol

nano search.php

Find the existing code marked with a comment saying "PATCH VULNERABLE CODE HERE", as shown below:

There are eight lines of vulnerable code (not counting comment lines), starting immediately below the "PATCH VULNERABLE CODE HERE" line, and ending with the "$error = $db_conn->ErrorMsg();" line.

Add a # character to the start of each line to comment out all the vulnerable code, as shown in the image below:

Add this code below the vulnerable code, as shown in the image below:

# SAFER CODE USING PARAMETERIZED QUERIES STARTS HERE

# PDO CONNECTION CODE
$dbConnection = new PDO('mysql:dbname=sqlol;host=127.0.0.1;charset=utf8', $username, $password);
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$where_clause = 'WHERE username LIKE :uname';
$query = "SELECT $column_name FROM $table_name $where_clause $group_by_clause $order_by_clause ";
$displayquery = $query;

$stmt = $dbConnection->prepare($query);
$qin = $_REQUEST['q'];
$stmt->execute(array(':uname' => $qin));

while ($row = $stmt->fetch()) {
print_r($row);
echo "<br>\n";
}

# END OF PARAMETERIZED QUERY CODE

Save the page with Ctrl+X, Y, Enter.

Testing the Fixed Code

In your Linux machine, in Firefox, go to

localhost/sqlol/search.htm

A "Find Users" page opens, as shown below:

In the Name field, type

O'Neil
Click the Submit button.

The error is gone, as shown below:

You can see that this is more powerful than the escaping technique. The search string O'Neil is unchanged, but it no longer causes a syntax error because it is not treated as text in a command-line. It passed as input to a query in a data structure that cannot be confused with active content.

This prevents SQL injection attacks, and it also allows you to properly store and retrieve names containing special characters.

Saving the Screen Image

Make sure the query of O'Neil is visible in the URL bar of the Firefox window, and that the table appears rather than a SQL error message.

Save a screen image with the filename Proj SQL-X4 from Your Name.

Turning in Your Project

Email the image to cnit.123@gmail.com with a Subject line of Proj SQL-X4 from Your Name.

Sources

http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php

Last modified: 4-26-16