There may not be quite as many different kinds of attacks as there are motives for attacks, but once again, there is more variety than might appear at first glance. This is especially true if the malicious user has found a way to carry out multiple query execution, a subject to which we will return in a moment.
If your script is executing a SELECT instruction, the attacker can force the display of every
row in a table by injecting a condition like 1=1 into the WHERE clause, with something like this (the injection is in bold):
SELECT * FROM wines WHERE variety = 'lagrein' OR 1=1;'
As we said earlier in this chapter, that can by itself be very useful information, for it reveals the general structure of the table (in a way that a single record cannot), as well as potentially displaying records that contain confidential information.
An UPDATE instruction has the potential for more direct damage. By inserting additional
properties into the SET clause, an attacker can modify any of the fields in the record being
updated, with something like this (the injection is in bold type):
UPDATE wines SET type='red','vintage'='9999' WHERE variety = 'lagrein'
And by adding an always-true condition like 1=1 into the WHERE clause of an UPDATE instruction, that modification can be extended to every record, with something like this (the injection is in bold type):
UPDATE wines SET type='red','vintage'='9999 WHERE variety = 'lagrein' OR 1=1;'
The most dangerous instruction may be DELETE, although it's not hard to imagine that a
buried and therefore overlooked change might in the long run be more destructive than a
wholesale deletion, which is likely to be immediately obvious. The injection technique is the
same as what we have already seen, extending the range of affected records by modifying the
WHERE clause, with something like this (the injection is in bold type):
DELETE FROM wines WHERE variety = 'lagrein' OR 1=1;'
Multiple-query injection multiplies the potential damage an attacker can cause, by allowing
more than one destructive instruction to be included in a query. The attacker sets this up by introducing an unexpected termination of the query. This is easily done with MySQL, where first an injected quotation mark (either single or double; a moment's experimentation will quickly reveal which) marks the end of the expected variable; and then a semicolon terminates that instruction. Now an additional attacking instruction may be added onto the end of the now-terminated original instruction. The resulting destructive query might look something like this (again, the injection, running over two lines, is in bold type):
SELECT * FROM wines WHERE variety = 'lagrein';GRANT ALL ON *.* TO 'BadGuy@%' IDENTIFIED BY 'gotcha';'
This exploit piggybacks the creation of a new user, BadGuy, with network privileges, all privi-
leges on all tables, and a facetious but sinister password, onto what had been a simple SELECT
statement. If you took our advice in Chapter 10 to restrict severely the privileges of process
users, this should not work, because the webserver daemon no longer has the GRANT privilege
that you revoked. But theoretically, such an exploit could give BadGuy free rein to do anything he wants to with your database.
There is considerable variability in whether such a multiple query will even be processed
by the MySQL server. Some of this variability may be due to different versions of MySQL, but
most is due to the way in which the multiple query is presented. MySQL's monitor program
allows such a query without any problem. The common MySQL GUI, phpMyAdmin, simply
dumps everything before the final query, and processes that only.
But most if not all multiple queries in an injection context are managed by PHP's mysql
extension. This, we are happy to report, by default does not permit more than one instruction to be executed in a query; an attempt to execute two instructions (like the injection exploit just shown) simply fails, with no error being set and no output being generated. It appears that this behavior is impossible to circumvent. In this case, then, PHP, despite its default hands-off behavior, does indeed protect you from the most obvious kinds of attempted injection.
PHP 5's new mysqli extension (see http://php.net/mysqli), like mysql, does not inherently permit multiple queries, but possesses a mysqli_multi_query() function that will let you do it if you really want to. If you decide that you do really want to, however, we urge you to remember that by doing so you are making an injector's job a lot easier.
The situation is more dire, however, with SQLite, the embeddable SQL database engine
that is bundled with PHP 5 (see http://sqlite.org/ and http://php.net/sqlite), and that has
attracted much attention recently for its ease of use. SQLite defaults to allowing such multiple-instruction queries in some cases, because the database can optimize batches of queries, particularly batches of INSERT statements, very effectively. The sqlite_query() function will not, however, allow multiple queries to be executed if the result of the queries is to be used by your script, as in the case of a SELECT to retrieve records (see the warning at ttp://php.net/ for more information).
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:
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):
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.
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
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!