SQL Injections

There are a few security concerns that you must consider when building SQL dynamically within your scripts. Lets consider that you have the table created with the SQL below:

CREATE TABLE `access` (
  `logonID` int NOT NULL AUTO_INCREMENT,
  `logon` varchar(45) DEFAULT NULL,
  `password` varchar(550) DEFAULT NULL,
  `accesslevel` int DEFAULT NULL,
  CONSTRAINT pk_access PRIMARY KEY (`logonID`));
With the following data:

INSERT INTO `access` VALUES
    (1,'admin','*84F2944DDF7CAC769127EB7746D37D399ED693AD',1),
    (231234,'m231234','*F13CA7723E675D1D7F8DBBBCDC7CA7699C8225E7',5),
    (234433,'m234433','*0C12DF007DB14AA9B637671745BCFDBA8D270D67',5);
Passwords can be verified via statement below which will return the user's access level (a made up construct in our program example), their logonID, and logon name if the password was successful, or nothing if the username password combination is invalid.

SELECT logonID, logon, accesslevel FROM access WHERE logon = 'admin' AND password = PASSWORD('badguess');
Empty set (0.00 sec)

SELECT logonID, logon, accesslevel FROM access WHERE logon = 'admin' AND password = PASSWORD('goodguess');
+---------+-------+-------------+
| logonID | logon | accesslevel |
+---------+-------+-------------+
|       1 | admin |           1 |
+---------+-------+-------------+
1 row in set (0.00 sec)
If the user enters a valid username / password combination their appropriate access level is returned by the query. Now lets assume that you have an HTML form that takes in the username and password (hopefully your are doing this via a secure HTTPS session...), here is the html for a very simple form.

<form method="POST" action="sql-injection.php">
  <center>
    <h2>SQL Injection Demo</h2>
    <table>
      <tr><td>logon</td><td><input type="text" name="logon"></td></tr>
      <tr><td>password</td><td><input type="password" name="password"></td></tr>
      <tr><td colspan="2"><center><input type="submit" value="LOGIN"></center></td></tr>
    </table>
  </center>
</form>

SQL Injection Demo

logon
password

In this example our PHP program takes in these inputs via the $_POST variable, if you have submitted anything via the form above, then your values were interpreted as:

$_POST['logon'] = ""
$_POST['password'] = ""
If your program were to dynamically build the SQL statement, it may looks something like:

$logon = $_POST['logon'];
$password = $_POST['password'];

$query = "SELECT logonID, logon, accesslevel FROM access WHERE logon='$logon' AND password=PASSWORD('$password');";
And $query would be evaluated as:

SELECT logonID, logon, accesslevel FROM access WHERE logon='' AND password=PASSWORD('');
So when this is evaluated by the MySQL server in this fashion, if the user were to enter in badly formatted input into one of the text boxes (either logon or password) it is possible that they will be able to get the database to perform an unwanted action or return undesirable results. Our demo considers the logon attempt to be valid if any results are returned, and the access level that the online user will have will be returned in the resulting table.

When this query ran, there were no results, so this script now assumes that the user a an invalid user.

Please log on to our super secure system!

We should review the resulting table (if any) that was returned by the database when this query ran.

Nothing was returned from the database query!

SQL Queries on Multiple Lines (Why commenting is hard)

If the programmer had formatted their query so that there were line-breaks in the query itself (nice formatting), you would have had considerably more difficulty in breaking into our super-secure system. MySQL has specific commenting styles:

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;
If the query had been written over multiple lines as shown above, your injection would have probably failed depending on the server's configuration and how it handles line breaks.

Multiple SQL Statements

If you tried to inject multiple SQL statements, PHP by default does not handle multiple SQL statements in a single query (the second, third, etc., will be ignored), unless the programmer specifically used multi_query() vice query(). This is not the default behavior, and hopefully no programmers are currently doing it this way! So the best you can hope to do is to log on as a user and get that information from the table results below!


xkcd.com (comic #327)

Storing Passwords and Protecting our Scripts

When storing passwords in the database, best practices suggest that we never store passwords in plain text. There are two simple methods to store passwords

Defending Against SQL Injections

As a programmer you have three options in your attempt to defend against SQL injections:

Client side scripts can never be the entire solution, the end user has the abilities do change any of the code on their end and submit what they wish. Server side scripting is safer, but you may not account for all of the possible inputs that evil (or unknowing) users could provide. SQL Prepared Statements are the best method to ensure that you query can not be subverted by bad input!

Within the MySQL terminal we use the PREPARE statement to create prepared statements that protect our data. Note: the examples below are all done from within MySQL, when we begin to connect to the database via PHP we will be using prepared statements within the PHP MySQL libraries that utilize these methods, we will not be providing these queries directly.

Lets look at the same query we have been using but using SQL PREPARE:

PREPARE chklgn FROM 'SELECT accesslevel FROM access WHERE logon=? AND password=PASSWORD(?)';
We then provide MySQL the individual values via variables:

SET @l = "admin";
SET @p = "workharder";
Then we EXECUTE the prepared statement USING the variables!

EXECUTE chklgn USING @l, @p;
When using this method, it will not be possible to inject any SQL into the query. With this example the variables @l and @p hold the logon and password respectively and the entire contents of either will be used when checking the logon or password!

The Open Web Application Security Project (OWASP) is a great resource for designing and building security into web applications. Check out thier SQL Injection Cheatsheet.