ITEC 325 2021spring flo

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

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

Solution: Sanitize Always call mysqli_real_escape_string on any user-provided string that is getting integrated into a database query (see the example program).

Note: Not only is the name “mysqli_real_escape_string” an awful name, it is surprising in another way: you must provide it two inputs (the database-connection as well as the string to sanitize). If you don't do this, it will print an easy-to-overlook warning (regardless of your error_reporting status!?!), and return NULL (which gets interpreted as the empty-string).

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 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.

   (Fwiw, other APIs/DB engines do allow this — for example using SQLite with python allows it.)

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.)

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—zoom (snow day)

This page licensed CC-BY 4.0 Ian Barland Page last generated

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