Preventing SQL Injection, Part 2 Page 2

INVISION POWER BOARD SQL INJECTION VULNERABILITY

Invision Power Board is a widely known forum system (see http://www.invisionboard.com for information). On 6 May 2005 a SQL injection vulnerability was found in the login code, by James Bercegay of GulfTech Security Research (see this page
for more information).

The login query is as follows:

$DB->query("SELECT * FROM ibf_members WHERE id=$mid AND password='$pid'");

The member ID variable $mid and the password ID variable $pid are retrieved from the my_cookie() function with these two lines:

$mid = intval($std->my_getcookie('member_id'));
$pid = $std->my_getcookie('pass_hash');

The my_cookie() function retrieves the requested variable from the cookie with this line:

return urldecode($_COOKIE[$ibforums->vars['cookie_id'].$name]);

The value returned from the cookie is not sanitized at all. While $mid is cast to an integer before being used in the query, $pid is left untouched. It is therefore subject to the kinds of injection we have discussed earlier.

This vulnerability was addressed by modifying the my_cookie() function as follows (in relevant part; see this page for more information):

if ( ! in_array( $name, array('topicsread', 'forum_read', 'collapseprefs') ) )
{
  return $this->
    clean_value(urldecode($_COOKIE[$ibforums->vars['cookie_id'].$name]));
}
else
{
  return urldecode($_COOKIE[$ibforums->vars['cookie_id'].$name]);
}

With this correction, the critical variables are returned after having been passed through the global
clean_value() function, while other variables are left (not inappropriately) unsanitized.
Source: http://www.securityfocus.com/archive/1/397672

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:

SELECT * FROM wines WHERE variety = 'lagrein'

Or in PHP:

$query = "SELECT * FROM wines WHERE variety = '$variety'";

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:

SELECT * FROM wines WHERE vintage =

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

SELECT * FROM wines WHERE vintage = ''

The second query will (presumably) return no results, but at least it will not return an error
message, as an unquoted empty value will (even though you have turned off all error reporting
to users–haven’t you? If not, look back at Chapter 11).

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. We discussed such validation at length in Chapter 11, to which we now refer you. 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 semi-
colon character (unless that punctuation mark could legitimately be included). You can do this
easily with the strpos() function, like this:

if ( strpos( $variety, ';' ) ) exit ( "$variety is an invalid value for variety!" );

As we suggested in Chapter 11, 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

Again, we discussed at length in Chapter 11 the escaping of dangerous characters. 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.

Come back next week when we continue with our excerpt from Pro PHP Security!

reprinted with permission by Apress