PHPBuilder - Preventing SQL Injection, Part 3 Page 2

RSS Twitter

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