The Web Application Hacker’s Handbook Discovering and Exploiting Security Flaws

of single quotation marks being inserted into the query, resulting in invalid

syntax.

270

Chapter 9 

■

Injecting Code

70779c09.qxd:WileyRed  9/14/07  3:13 PM  Page 270

---

Second-Order SQL Injection

A particularly interesting type of filter bypass arises in connection with second-

order SQL injection. As described earlier, it is very common for applications to

seek to defend themselves against SQL injection by escaping any single quota-

tion marks that appear within string-based user input (and rejecting any that

appear within numeric input). Even when this approach is not vulnerable in

the ways already described, it can sometimes be bypassed.

In the original book search example, this approach appears to be effective.

When the user enters the search term 

O’Reilly

, the application makes the fol-

lowing query:

SELECT author,title,year FROM books WHERE publisher = ‘O’‘Reilly’

Here, the single quotation mark supplied by the user has been converted

into two single quotation marks, and so the item passed to the database has the

same literal significance as the original expression entered by the user.

One problem with the doubling-up approach arises in more complex situa-

tions where the same item of data passes through several SQL queries, being

written to the database and then read back more than once. This is one exam-

ple of the shortcomings of simple input validation as opposed to boundary vali-

dation, as described in Chapter 2.

Recall the application that allowed users to self-register and contained a

SQL injection flaw in an 

INSERT

statement. Suppose that developers attempt to

fix the vulnerability by doubling up any single quotation marks which appear

within user data. Attempting to register the username 

foo’

results in the fol-

lowing query, which causes no problems for the database:

INSERT INTO users (username, password, ID, privs) VALUES (‘foo’‘’,

‘secret’, 2248, 1)

So far, so good. However, suppose that the application also implements a pass-

word change function. This function is only reachable by authenticated users, but

for extra protection, the application requires users to submit their old password.

It then verifies that this is correct by retrieving the user’s current password from

the database and comparing the two strings. To do this, it first retrieves the user’s

username from the database and then constructs the following query:

SELECT password FROM users WHERE username = ‘foo’‘

Because the username stored in the database is the literal string 

foo’

, this is

the value that the database returns when this value is queried — the doubled-

up escape sequence is only used at the point where strings are passed into the

database. Therefore, when the application reuses this string and embeds it into

a second query, a SQL injection flaw arises and the user’s original bad input is