Preventing SQL Injection, Part 3

Chris Snyder and Michael Southwell

Chapter 12 is reprinted with permission by Apress

Abstract to Improve Security

We do not suggest that you try to apply the techniques listed earlier manually to each instance of user input. Instead, you should create an abstraction layer. A simple abstraction would incorporate your validation solutions into a function, and would call that function for each item of user input. A more complex one could step back even further, and embody the entire process of creating a secure query in a class. Many such classes exist already; we discuss some of them later in this chapter.

Such abstraction has at least three benefits, each of which contributes to an improved
level of security:

1. It localizes code, which diminishes the possibility of missing routines that circumstances
   (a new resource or class becomes available, or you move to a new database with different
   syntax) require you to modify.
2. It makes constructing queries both faster and more reliable, by moving part of the work
   to the abstracted code.
3. When built with security in mind, and used properly, it will prevent the kinds of
   injection we have been discussing.

Retrofitting an Existing Application

A simple abstraction layer is most appropriate if you have an existing application that you wish to harden. The code for a function that simply sanitizes whatever user input you collect might look something like this:

function safe( $string ) {
  return "'" . mysql_real_escape_string( $string ) . "'"
}

Notice that we have built in the required single quotation marks for the value (since they
are otherwise hard to see and thus easy to overlook), as well as the
mysql_real_escape_string() function. This function would then be used to construct a $query
variable, like this:

$variety = safe( $_POST['variety'] );
$query = "SELECT * FROM wines WHERE variety=" . $variety;

Now your user attempts an injection exploit by entering this as the value of $variety:

lagrein' or 1=1;

To recapitulate, without the sanitizing, the resulting query would be this (with the injection
in bold type), which will have quite unintended and undesirable results:

SELECT * FROM wines WHERE variety = 'lagrein' or 1=1;'

Now that the user’s input has been sanitized, however, the resulting query is this harmless one:

SELECT * FROM wines WHERE variety = 'lagrein' or 1=1;'

Since there is no variety field in the database with the specified value (which is exactly
what the malicious user entered: lagrein’ or 1=1;), this query will return no results, and the
attempted injection will have failed.

Securing a New Application

If you are creating a new application, you can start from scratch with a more profound layer of abstraction. In this case, PHP 5’s improved MySQL support, embodied in the brand new mysqli
extension, provides powerful capabilities (both procedural and object-oriented) that you
should definitely take advantage of. Information about mysqli (including a list of configuration options) is available at http://php.net/mysqli. Notice that mysqli support is available only if you have compiled PHP with the –with-mysqli=path/to/mysql_config option.
A procedural version of the code to secure a query with mysqli follows, and can be found
also as mysqliPrepare.php in the Chapter 12 folder of the downloadable archive of code for Pro PHP Security at http://www.apress.com.

<?php
// retrieve the user's input
$animalName = $_POST['animalName'];

// connect to the database
$connect = mysqli_connect( 'localhost', 'username', 'password', 'database' );
if ( !$connect ) exit( 'connection failed:  ' . mysqli_connect_error() );
// create a query statement resource
$stmt = mysqli_prepare( $connect,
 "SELECT intelligence FROM animals WHERE name = ?" );
if ( $stmt ) {
  // bind the substitution to the statement
  mysqli_stmt_bind_param( $stmt, "s", $animalName );
  // execute the statement
  mysqli_stmt_execute( $stmt );
  // retrieve the result...
  mysqli_stmt_bind_result( $stmt, $intelligence );
  // ...and display it
  if ( mysqli_stmt_fetch( $stmt ) ) {
    print "A $animalName has $intelligence intelligence.n";
  } else {
    print 'Sorry, no records found.';
  }
  // clean up statement resource
  mysqli_stmt_close( $stmt );
}
mysqli_close( $connect );
?>

The mysqli extension provides a whole series of functions that do the work of constructing and
executing the query. Furthermore, it provides exactly the kind of protective escaping that we
have previously had to create with our own safe() function. (Oddly, the only place this
capacity is mentioned in the documentation is in the user comments at this page.)

First you collect the user’s submitted input, and make the database connection. Then you
set up the construction of the query resource, named $stmt here to reflect the names of the
functions that will be using it, with the mysqli_prepare() function. This function takes two
parameters: the connection resource, and a string into which the ? marker is inserted every
time you want the extension to manage the insertion of a value. In this case, you have only one such value, the name of the animal.

In a SELECT statement, the only place where the ? marker is legal is right here in the comparison value. That is why you do not need to specify which variable to use anywhere except in the mysqli_stmt_bind_param() function, which carries out both the escaping and the substitution; here you need also to specify its type, in this case “s” for “string” (so as part of its provided protection, this extension casts the variable to the type you specify, thus saving you the effort and coding of doing that casting yourself). Other possible types are “i” for integer, “d” for double (or float), and “b” for binary string.

Appropriately named functions, mysqli_stmt_execute(), mysqli_stmt_bind_result(), and
mysqli_stmt_fetch(), carry out the execution of the query and retrieve the results. If there are results, you display them; if there are no results (as there will not be with a sanitized attempted injection), you display an innocuous message. Finally, you close the $stmt resource and the database connection, freeing them from memory.