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

Requirements

Purpose

Parameterized Queries provide better defense.

Configuring and Starting MySQL You may already have done this, but if your NETLAB session ended, you'll need to do it again. In a Terminal window, enter this command: dpkg-reconfigure mysql-server-5.5 A box asks you for the "New password". Type in a password of password and press Enter In the next box, type in a password of password and press Enter This is obviously an insecure password, but use it anyway for this project, to match the password SQLol expects. In a Terminal window, enter these commands: service mysql start netstat -pant MySQL starts, and you should see it listening on port 3306, as shown below: Restarting Apache In a Terminal window, enter this command: service apache2 restart Downloading and Installing SQLol The original SQlol software was developed by spiderlabs. I modified it slightly and put a copy on my Web server--that's the version that makes this project easiest. In your Linux machine, in a Terminal window, enter these commands: cd /var/www/html curl https://samsclass.info/124/proj14/sqlol-sbowne.tgz >sqlol-sbowne.tgz tar xzf sqlol-sbowne.tgz In your Linux machine, from the menu bar, click Applications, Internet, "IceWeasel Web Browser". (Note: Some Linux versions have Firefox instead). In IceWeasel, go to localhost/sqlol SQLol opens, as shown below: Click RESET A message appears saying "Done!" If you see the "Could not connect..." message shown below, restart mySQL with this command: service mysql restart

Testing the "Find Users" Page

localhost/sqlol/search.htm

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

In the Name field, type

O'Neil

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

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

localhost/sqlol/search.htm

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

In the Name field, type

O'Neil

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.

Sources

Last modified: 4-26-16
Revised for NETLAB 6-16-16