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.
Given a legitimate user input of "lemming," this routine will (assuming appropriate data in the database) print the message "A lemming has very low intelligence." Given an attempted injection like "lemming' or 1=1;" this routine will print the (innocuous) message "Sorry, no records found."
The mysqli extension provides also an object-oriented version of the same routine, and we demonstrate here how to use that class. This code can be found also as mysqliPrepareOO.php in the Chapter 12 folder of the downloadable archive of code for Pro PHP Security at http://www.apress.com.
<?php
$animalName = $_POST['animalName'];
$mysqli = new mysqli( 'localhost', 'username', 'password', 'database');
if ( !$mysqli ) exit( 'connection failed:  ' . mysqli_connect_error() );
$stmt = $mysqli->prepare( "SELECT intelligence
  FROM animals WHERE name = ?" );
if ( $stmt ) {
  $stmt->bind_param( "s", $animalName );
  $stmt->execute();
  $stmt->bind_result( $intelligence );
  if ( $stmt->fetch() ) {
    print "A $animalName has $intelligence intelligence.\n";
  } else {
    print 'Sorry, no records found.';
  }
  $stmt->close();
}
$mysqli->close();
?>
This code duplicates the procedural code described previously, using an object-oriented syntax and organization rather than strictly procedural code.
Full Abstraction
If you use external libraries like PearDB (see http://pear.php.net/package/DB), you may be wondering why we are spending so much time discussing code for sanitizing user input, for those libraries tend to do all of the work for you. The PearDB library takes abstraction one step beyond what we have been discussing, not only sanitizing user input according to best practices, but also doing it for whatever database you may happen to be using. It is therefore an extremely attractive option if you are concerned about hardening your scripts against SQL injection. Libraries like PearDB offer highly reliable (because widely tested) routines in a highly portable and database-agnostic context.
On the other hand, using such libraries has a clear downside: it puts you at the mercy of someone else's idea of how to do things, adds tremendously to the quantity of code you must manage, and tends to open a Pandora's Box of mutual dependencies. You need therefore to make a careful and studied decision about whether to use them. If you decide to do so, at least you can be sure that they will indeed do the job of sanitizing your users' input.
Test Your Protection Against Injection
As we discussed in previous chapters, an important part of keeping your scripts secure is to test them for protection against possible vulnerabilities. The best way to make certain that you have protected yourself against injection is to try it yourself, creating tests that attempt to inject SQL code. For help and guidance in this task, you will probably find it useful to consult the amusing and revealing detailed instructions on just how to carry out an injection exploit, which can be found at here and here.
Here we present a sample of such a test, in this case testing for protection against injection into a SELECT statement. This code can be found also as protectionTest.php in the Chapter 12 folder of the downloadable archive of code for Pro PHP Security at http://www.apress.com.
<?php
// protection function to be tested
function safe( $string ) {
  return "'" . mysql_real_escape_string( $string ) . "'"
}
// connect to the database
///////////////////////
// attempt an injection
///////////////////////
$exploit = "lemming' AND 1=1;";
// sanitize it
$safe = safe( $exploit );

$query = "SELECT * FROM animals WHERE name = $safe";
$result = mysql_query( $query );
// test whether the protection has been sufficient
if ( $result && mysql_num_rows( $result ) == 1 ) {
  exitt "Protection succeeded:\n
    exploit $exploit was neutralized.";
}
else {
  exit( "Protection failed:\n
    exploit $exploit was able to retrieve all rows." );
}
?>
If you were to create a suite of such tests, trying different kinds of injection with different SQL commands, you would quickly detect any holes in your sanitizing strategies. Once those were fixed, you could be sure that you have real protection against the threat of injection.
Summary
We began here in Chapter 12 our examination of specific threats to your scripts caused by faulty sanitizing of user input, with a discussion of SQL injection. After describing how SQL injection works, we outlined precisely how PHP can be subjected to injection. We then provided a real-life example of such injection. Next we proposed a series of steps that you can take to make attempted injection exploits harmless, by making sure that all submitted values are enclosed in quotation marks, by checking the types of user-submitted values, and by escaping potentially dangerous characters in your users' input. We recommended that you abstract your validation routines, and provided scripts for both retrofitting an existing application and securing a new one. Then we discussed the advantages and disadvantages of third-party abstraction solutions.
Finally, we provided a model for a test of your protection against attempted SQL applica- tions resulting in injection. We turn in Chapter 13 to the next stage of validating user input in order to keep your PHP scripts secure: preventing cross-site scripting.
reprinted with permission by Apress