PHPBuilder - Preventing SQL Injection, Part 3 Page 2

RSS Twitter
Articles Application Architecture

Preventing SQL Injection, Part 3 - Page 2

by: PHP Builder Staff
November 15, 2006

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
$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->bind_result( $intelligence );
  if ( $stmt->fetch() ) {
    print "A $animalName has $intelligence intelligence.\n";
  } else {
    print 'Sorry, no records found.';
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, 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
// 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.
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

« Previous Page

Comment and Contribute

Your comment has been submitted and is pending approval.




(Maximum characters: 1200). You have characters left.