ITEC 325 2017spring ibarland

home—lects—hws
D2L—breeze (snow day)

mysqli-sanitizing
sanitizing DB queries
quoting; prepared statements

SQL Injection Attacks

Here is some typical code which validates a user, by looking up their name/password in a table we have previously created:

$user = $_POST['username'];
$passwd = $_POST['password'];
$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='$user' and pass='$passwd'";  // DANGER!  SQL injection possible.
$result = mysqli_query($connect,$qry);           // A table object (possibly w/ 0 rows); NULL on query-failure.
if (mysqli_fetch_assoc($result)) {    // Safer: `if (mysqli_num_rows($result) == 1) ...`
    // login successful -- we found that name/password in our db!
    }
     

$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='fred' and pass='xyz123'";
     

BUT: What if the user had typed in a single-quote in their input? Suppose they typed Shaquille O'Neal — why will the query fail?

$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='Shaquille O'Neal' and pass='sugarshaq'";
     

WORSE: Can the user type in something so that the SELECT statment always returns a result?
Alas, yes! Something like: ' or 'hi'='hi leads to a query that always turns true.

$qry = "SELECT * 
        FROM MyPasswords
        WHERE name='fred' and pass='' or 'hi' = 'hi'";1
     

Warning: Always call mysqli_real_escape_string on any user-provided string that is getting integrated into a database query.

Now you may feel that if you've already done other validation on the data and know it doesn’t contain any single-quotes or other characters SQL might mis-interpret, then you're safe. That’s true. However, you should still call mysqli_real_escape_string! The primary reason is changing specs: When Shaquille O’Neal calls your boss to complain that your web form doesn’t allow apostrophes, and your boss orders some schmo new programmer to change the validation-code for the form to allow apostrophes, it’s easy to to miss that you have suddenly opened your site to SQL injection attacks!

(Another reason to call mysqli_real_escape_string even if you can reason to yourself that it’s not necessary: Anybody reviewing your code who sees a lack of calling that function would otherwise spend minutes/hours scrutinizing the code to check for possible attacks, perhaps searching through old logs, and unnecessarily worrying about whether they need to notify their entire customer base of a potential data leak.)

There are even multiple to cartoons reminding you to sanitize your SQL queries.

Prepared statements

Why use prepared statements (in order of importance):

1. Improved correctness: They sanitize inputs for you.
2. Improved performance: The database-engine can optimize (compile) the query just once, and doesn’t need to re-optimize every time you repeatedly-call the prepared query.

What they are: SQL statements where you replace specific values with a question-mark (“?”).
Example: SELECT * from Inventory where (product=?).

Note:Don’t confuse “prepared statement” with “stored procedure” — the latter is just a PL/SQL function. (They are similar though — both set up what is in essence a function-call, so you can just call it with actual-parameters later. However, a prepared-statement only lives while you keep a reference to it while the stored-procedure is something the database remembers forever.)

How to use prepared statments:

Example: See the second half of mysqli-example.php.

In php, prepared statements are a bit convoluted. You have to:

1. Create the prepared statement (one-time setup).

   $stmt = mysqli_stmt_init($link); $stmt = mysqli_stmt_prepare($stmt, "SELECT numOnShelf,seller from Inventory where Product=? and containerSize>?");

   Since these two statements are commonly used together, there is another function that effects the exact same thing:

   $stmt = mysqli_prepare($link, "SELECT numOnShelf,seller from Inventory where Product=? and containerSize>?");

   Note that, out of all the functions handling prepared-statements, “mysqli_prepare” is the only name that name doesn’t contain “_stmt_” nor require a statement-object as its first argument.
2. At this point, you’d think you're pretty much set, and you could do something reasonable like “$resultTable = wishfullyExecutePreparedStmt( $stmt, "M&M's", 16)”². You can’t do this in PHP. For reasons I don't understand, we have to jump through further hoops: binding variables.
3. Bind php-variables to each the sql-parameters.
   (If your statement doesn't have any input-parameters “?”, you can skip this step.)

   In php, you must specify the type of each input (which is the second argument to mysqli_stmt_bind_param — s for string and d for integer, just like printf):

   mysqli_stmt_bind_param($stmt, 'sd', $productName, $minSize); $productName = "M&M's"; $minSize = 16;

   NOTE: this is very odd: The query will later remember to always use the contents of the particular variables $productName and $minSize, whan making the query! (In particular, mysqli_stmt_bind_param($stmt, 'sd', "M&M's", 16); does not work?!?!, since it passes literal strings/ints, rather than a raw variable.)

   Note that the contents of the input-variables (“productName” and “minSize” in this case) are SQL-sanitized for you! You don't have to call mysql_real_escape_string. (In fact, you definitely don't want to call that function if you're using prepared statements, since that would quote any quote-characters!)

4. To actually run the query: mysqli_stmt_execute($stmt).
   If you want to repeat this query multiple times (with different “inputs”), you don't re-bind any variables; you just change the contents of the input-variables and then call mysqli_stmt_execute again, and it will use the revised values.
   (Again: What a weird approach — using those bind-statements isn't any more programmer-friendly than just passing and returning values, but it's a wildly different paradigm. Why bother? Sigh...)

5. except, php on rucs...:This doesn't currently work on rucs; see the next item.

   To process the results of the table, call mysqli_stmt_get_result($stmt) and then you can just loop through the result-table, as seen earlier:

   $allRows = mysqli_stmt_get_result($stmt); while ($oneRow = mysqli_fetch_assoc($allRows)) { echo $oneRow['custName'], " bought ", $oneRow['product'];

6. except, php on rucs…: Alas, the above function mysqli_stmt_get_result is only in PHP ≥ 5.3; rucs is still running the archaically outdated PHP 5.2.8.

   If you are stuck using really old PHP, then we can also use bind-variables for our results as well as the query’s inputs:
   • Before executing the prepared-statement, bind output variables: mysqli_stmt_bind_result($stmt, $col1, $col2);.
   • Then, run the statement with mysqli_stmt_fetch($stmt); the first row's query-results will magically be stored into your variables $col1 and $col2. You can call that function repeatedly, paying attention to its return value:

     while (mysqli_stmt_fetch($stmt)) { echo "I got $col1 and $col2!\n"; }

   (Again, see the second half of mysqli-example.php.)
7. When done, close the prepared-statement: mysqli_stmt_close($stmt).
   (This lets the db release any associated resources — namely, the associated query-optimization-plan.)

Securing the data stored in the database.

Suppose we store passwords or credit-cards in our database. What might someday happen (inferring from headlines)? Sadly, somebody might breach the db itself (aside from the website entirely) — either a hacker exploiting a DB security hole, or somebody accidentaly revealing the DB account password, or an inside employee stealing data.

Solution: Don't store the password in the database!
“But Barland, how the heck can we verify that they've typed in the correct password?”
We'll instead store a hashed form of the password. Then, when a user later types in the password on a form, we'll hash whatever they type, and see if the two hashes are equal.

php > echo hash('sha256', 'hello');
2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
php > echo hash('sha256', 'helmo');
05c21a42da66f4e2d217e35b74ef9dc2d65c8d46f9aec0d6397a65dd3c766f21
     

Q1: If my form hashes the password client-side, and only sends the hash to the server to be verified, this is secure, right?
A1: No!
If an eavesdropper can overhear the hash being communicated, the server only sees/checks the hash to determine if a user knows the password, then such an eavesdropper would be able to fool the server!
So, even if using hashed passwords, be sure to use a secure (https) connection whenever sensitive information is transmitted.

Q2: Should you hash the password client-side, or server-side?
A2: server-side!
Although either approach protects against people guessing passwords, if your database were breached and an attacker glimpsed the hashed-passwords, you wouldn’t want them to be able to log in as another user. By having the password submitted to the server (over https of course), and having the server hash the string, it means that knowing the hash isn’t enough — an attacker would need to know some string which hashes to what was found in the database — and that’s exactly what secure hashes make difficult/impossible! (Of course, if an attacker has breached the database, you might have much bigger problems — did the attacker modify the database-contents, including changing other people’s passwords to hash('sha256','ha-ha, pwned!').)

Salting the hash

Of course, to help protect against brute-force dictionary attacks, you want to add a few extra "salt" characters to the password — say, 20 characters. And ideally, these should be different characters for each person (else, if an attacker found out the one salt you used, they could use that on every word in the dictionary and look for matches). … But it’s admittedly a bit of a hassle, to generate a salt for each user, and store that in the database as well as the hashed password.

Solution:

except, php on rucs...:Unfortunately, rucs’s old verion of php does not support the functions below, so there you have to generate a salt, and store it, yourself.

         php > echo password_hash("kittee",PASSWORD_BCRYPT);
         $2y$10$Pj/26IW2mmF7LrA7WZGKeOf7YMIX5EKT7h8bhoBCwQs67PB5DDhUi
         php > echo password_verify("kittee",'$2y$10$Pj/26IW2mmF7LrA7WZGKeOf7YMIX5EKT7h8bhoBCwQs67PB5DDhUi');
         1
         php > echo password_verify("doge",'$2y$10$Pj/26IW2mmF7LrA7WZGKeOf7YMIX5EKT7h8bhoBCwQs67PB5DDhUi');
         php > echo password_hash("kittee",PASSWORD_BCRYPT);
         $2y$10$Ije9mY4Mm9LwEFWSySGBmOGjzg1/Y6VLYLPnsmWVuWHzoYdiz5lyG
       

stmt.setString(1,"M&M's");
stmt.setInt(2,16);
boolean ok = stmt.executeQuery();
if (ok) { ResultSet rs = stmt.getResultSet(); …processRows(rs)… } else { …handleError()… }
            

home—lects—hws
D2L—breeze (snow day)

©2017, Ian Barland, Radford University Last modified 2017.Apr.07 (Fri)

Please mail any suggestions (incl. typos, broken links) to ibarlandradford.edu