PHPBuilder - Preventing SQL Injection, Part 2

RSS Twitter

Preventing SQL Injection, Part 2

by: PHP Builder Staff
November 8, 2006

Chapter 12 is reprinted with permission by Apress

Kinds of Injection Attacks

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

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, 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 and, 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:// for more information).

Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.




(Maximum characters: 1200). You have characters left.