Now that we have surveyed just what SQL injection is, how it can be carried out, and to what extent you are vulnerable to it, let’s turn to considering ways to prevent it. Fortunately, PHP has rich resources to offer, and we feel confident in predicting that a careful and thorough application of the techniques we are recommending will essentially eliminate any possibility of SQL injection in your scripts, by sanitizing your users’ data before it can do any damage.

Demarcate Every Value in Your Queries

We recommend that you make sure to demarcate every single value in your queries. String values must of course be delineated, and for these you should normally expect to use single (rather than double) quotation marks. For one thing, doing so may make typing the query easier, if you are using double quotation marks to permit PHP’s variable substitution within the string; for another, it (admittedly, microscopically) diminishes the parsing work that PHP has to do to process it.

We illustrate this with our original, noninjected query:

Or in PHP:

Quotation marks are technically not needed for numeric values. But if you were to decide not to bother to put quotation marks around a value for a field like vintage, and if your user entered an empty value into your form, you would end up with a query like this:

This query is, of course, syntactically invalid, in a way that this one is not:

The second query will (presumably) return no results, but at least it will not return an error message, as an unquoted empty value will.

Check the Types of Users’ Submitted Values

We noted previously that by far the primary source of SQL injection attempts is an unexpected form entry. When you are offering a user the chance to submit some sort of value via a form, however, you have the considerable advantage of knowing ahead of time what kind of input you should be getting. This ought to make it relatively easy to carry out a simple check on the validity of the user’s entry. Here we will simply summarize what we said there. If you are expecting a number (to continue our previous example, the year of a wine vintage, for instance), then you can use one of these techniques to make sure what you get is indeed numeric:

• Use the is_int() function (or is_integer() or is_long(), its aliases).
• Use the gettype() function.
• Use the intval() function.
• Use the settype() function.

To check the length of user input, you can use the strlen() function.

To check whether an expected time or date is valid, you can use the strtotime() function. It will almost certainly be useful to make sure that a user’s entry does not contain the semicolon character (unless that punctuation mark could legitimately be included). You can do this easily with the strpos() function, like this:

A careful analysis of your expectations for user input should make it easy to check many of them.

Escape Every Questionable Character in Your Queries

We simply reiterate here our recommendations, and refer you back there for details:

• Do not use the magic_quotes_gpc directive or its behind-the-scenes partner, the addslashes() function, which is limited in its application, and requires the additional step of the stripslashes() function.
• The mysql_real_escape_string() function is more general, but has its own drawbacks.

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 article.

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:

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:

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:

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

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.

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 http://us2.php.net/mysqli_stmt_bind_param.)

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 providedprotection, 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 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.