#native_company# #native_desc#
#native_cta#

Preventing SQL Injection, Part 2

By PHP Builder Staff
on 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 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).