There are three things you should never ever do in PHP:

1. Use short tags (<? and ?>) or ASP-style tags (<% and %>) rather than the standard <?php and ?>
2. Use the addslashes or stripslashes functions
3. Enable magic_quotes_gpc or magic_quotes_runtime

Actually there are a lot more things you should never ever do in PHP, but we’ll start with these.

The first rule should be self-explanatory. Many servers have short tags disabled, and I doubt there’s a single server in the entire universe with ASP-style tags enabled, so the only way to ensure that your application will work everywhere is to use the standard long tags.

I could write several articles all about the evils of magic quotes and why doing the slash-dance with addslashes and stripslashes is a surefire way to produce unsecure and unmaintainable code, but plenty of articles have already been written on this subject. Suffice it to say that if you find yourself using addslashes or stripslashes, it’s time to stop and rethink the design of your application. You should never need to use these functions. Ever. I’m about to describe a much better (and far easier) way to protect yourself from SQL injection attacks.

Now, let’s pretend we’re developing a web portal for monkeys and we’re calling it, oddly enough, Monkey.

First we’ll create a common script that will be included by every other script in our application. In this file, we’ll define our database settings, include our application’s classes, and initialize our framework. Because we’re so original, we’ll call it common.php. This concept shouldn’t be anything new to you if you’ve been developing PHP apps for any length of time.

common.php:

<?php
// Database settings.
define('DB_HOST', 'localhost');
define('DB_USER', 'myuser');
define('DB_PASS', 'mypass');
define('DB_NAME', 'monkey');

// Load classes.
require_once 'classes/Monkey.php';

// Initialize the application.
Monkey::init();
?>

Notice the last line: we’ve made a call to the static function init of class Monkey. This is the base class of our application, and init is the function that’ll open a database connection and get everything ready to go.

We’re using PHP 5 because we’re not silly, so we’ll make Monkey an abstract class. In essence, the Monkey class will serve as our application’s main namespace, keeping our own methods and variables separate from those of PHP. Since PHP’s global namespace is incredibly polluted, this will make our life easier down the road.

The init function will do two things: connect to the database and load an XML file containing all the SQL queries our application will be using. By keeping our queries in this XML file, separate from our code, we’ll make our application vastly more maintainable. Mixing SQL queries with PHP code makes for a big mess. In addition, we’ll use an incredibly simple (but powerful) form of parameter substitution to ensure that every single piece of data we send to MySQL will be properly escaped, thus protecting us from SQL injection attacks, all with almost no effort on our part.

Here’s an example showing how our XML file (which we’ll name queries.xml) might look:

db/queries.xml

<?xml version="1.0"?>
<queries>
  <!-- Table: user -->
  <query name="user.getByUsername">
    <![CDATA[
      SELECT *
      FROM user
      WHERE username = :username
    ]]>
  </query>

  <query name="user.insert">
    <![CDATA[
      INSERT INTO user (
        username,
        password,
        email,
        fur_color,
        height,
        weight
      )
      VALUES (
        :username,
        :password,
        :email,
        :fur_color,
        :height,
        :weight
      )
    ]]>
  </query>
</queries>

Note that you’ll want to either restrict access to the directory containing queries.xml or place it outside your web directory (I forgot to mention this in the first version of this article; thanks to Brett for pointing it out). You wouldn’t want the whole world viewing your SQL queries.

As you can see, we’ve substituted placeholders like :username and :email for incoming values. We’ve also named our queries descriptively in the form “table.action”. Later, when we start executing these database queries from PHP, this will help us understand what the queries are doing. If we were using MySQL 5 or PostgreSQL, we could accomplish something similar using stored procedures, but I actually tend to prefer this method (mostly because I find stored procedures harder to maintain and a bit overkill for most purposes).

Now that we’ve created our query file, let’s create our Monkey class, along with the init function and a query function, which we’ll call whenever we want to execute a database query.

classes/Monkey.php

<?php
abstract class Monkey
{
  public static $db;
  public static $queries;

  public static function init()
  {
    // Open database connection.
    self::$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    // Load predefined queries.
    $queriesXml = simplexml_load_file('db/queries.xml');

    self::$queries = array();

    foreach($queriesXml as $query)
      self::$queries[(string)$query['name']] = (string)$query;
  }

  public static function query($name, $parameters = array())
  {
    if (!isset(self::$queries[$name]))
      throw new Exception("Undefined query: $name");

    $sql = self::$queries[$name];

    if (count($parameters))
    {
      $formattedParams = array();

      // Prepend a ':' to each parameter name and escape the value properly.
      foreach($parameters as $paramName => $paramValue)
      {
        if (!is_numeric($paramValue))
        {
          if (is_null($paramValue))
            $paramValue = 'NULL';
          else
            $paramValue = "'".self::$db->real_escape_string($paramValue)."'";
        }

        $formattedParams[":$paramName"] = $paramValue;
      }

      // Replace placeholders in the query with assigned values.
      $sql = strtr($sql, $formattedParams);
    }

    // Display the query if SHOWSQL is true (for debugging purposes).
    if (defined('SHOWSQL') && SHOWSQL)
      echo htmlentities($sql)."<br />\n";

    // Execute the query and return the result.
    return self::$db->query($sql);
  }
}
?>

Take a close look at the query function. This little function will save us tons of time and effort since it will do all the work of executing SQL queries and automatically escaping our input data.

Mull this over in your head a bit. Think about how you’d go about using the query function. Then check back tomorrow for the next part, in which I’ll provide some examples.