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:
A box asks you for the "New password".dpkg-reconfigure mysql-server-5.5
Type in a password of
and press Enterpassword
In the next box, type in a password of
and press Enterpassword
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:
MySQL starts, and you should see it listening on port 3306, as shown below:service mysql start
netstat -pant
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:
In your Linux machine, from the menu bar, click Applications, Internet, "IceWeasel Web Browser". (Note: Some Linux versions have Firefox instead).cd /var/www/html
curl https://samsclass.info/124/proj14/sqlol-sbowne.tgz >sqlol-sbowne.tgz
tar xzf sqlol-sbowne.tgz
In IceWeasel, go to
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
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:
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.
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.
Last modified: 4-26-16
Revised for NETLAB 6-16-16