Anatomy of an SQL Injection Attack

Security is everyone’s problem. It is important to be aware of issues that can foster security violations in software. Buffer Overflows, a common software security hole, arise from the length of input not being checked. When the input is larger than the memory allocated, the input data can spill over into unintended memory addresses. By appending a command with the correct offset, it is quite possible to push the command into memory space with high level privileges and execute.

In a buffer overflow attack, often the application accepting the input is running under reduced privileges. Because the input overflows the given memory address, it matters not that the input originated from a low privilege application, but rather the actual memory address where the command is stored and executed.

SQL injection is another type of attack and shares a common root with Buffer Overflow attacks. When input is not properly evaluated and filtered, bad things can happen. In an SQL injection attack, the attacker appends SQL statements to input. Here is a simple query:


SELECT userID, username, password
FROM Users
WHERE UserID = 1


Here is an example of appending a command to an SQL statement.


SELECT userID, username, password
FROM Users
WHERE UserID = 1; DROP Users;


In the last example, an SQL command to drop the users table was added. Imagine for a moment the URL to access a user profile.
http://someserver/index.cfm?userID=1
The userID is appended to the URL and is undoubtedly passed to a query in the application that returns the profile associated with UserID 1.

To create an SQL Injection attack with the URL above, we could simply try the following URL: http://someserver/index.cfm?userID=1;drop users
When the application substitutes the userID value of 1;drop users in the query, there are actually two statements to be executed. Firstly, the command to return the data from the users table associated with userID 1. Secondly, the command to drop the whole users table.

Pragmatically speaking, there would be little to gain by dropping the users table apart from vandalism. That being said, there are thousands of 5kr1pt k1dd135 whom would be delighted in dropping your users table for you and then bragging to their little wanker friends about how they trashed your server. Still, not much of a security risk? Let us try another angle.

Suppose for a moment a site that charged a lot of money for access to data. Users periodically purchased subscriptions and your organization was making millions. Inside the database was a users table with the field of ‘ExpirationDate’ representing the date the subscription would need renewal. Shall we form an attack to give us a 20 year subscription?

This is the SQL we wish to execute


SELECT userID, username, password
FROM Users
WHERE UserID = 1; update Users set ExpirationDate = '5/5/2027';


Can you guess what the URL string would look like?

If you guessed: http://someserver/index.cfm?userID=1;update users set expirationdate = ‘5/5/2027’

Then you are close. We may need to massage the url a little, or find a text input to put our command if the spaces and quotes are not respected.

So we can bump our subscription up 20 years or so. What else can we do? Let us try to add a user.

This is the SQL we wish to execute


SELECT userID, username, password
FROM Users
WHERE UserID = 1; insert into Users (username, password, expirationdate) values ('imahaxor','inyourbox','5/5/2027');

http://someserver/index.cfm?userID=1;insert into Users (username, password,expirationdate) values (‘imahaxor’,’inyourbox’,’5/5/2027′);

Now in place of just extending a subscription, the attacker has a new account that won’t expire for a while yet. Not a pretty picture is it?

How can you defend against SQL injection attacks, you ask? There are some best practices you may follow to reduce your attack surface. Let us look at a few:

1. Reduce the privileges given to the SQL user of your application. If the application never needs to insert into a particular table, then remove that privilege.
2. Check your input values. If you are expecting a number, add val() around the value. In the case above, a simple val() statement turns a malicious statement into a simple 0. We all like 0, right?
3. Use prepared statements. Adding cfqueryparam values to your dynamic query values adds great protection. Apart from escaping malicious characters, the prepared statement treats the value as a value, not a string of text to be executed by the SQL engine.

While software is extremely difficult to secure completely you can remove a giant risk by gaining understanding of SQL Injection Attacks and using the techniques above.

Below are some examples of a recent attempt to use SQL Injection on my blog. This attack was not very sophisticated, but could have disrupted the services of this blog.


Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and 1=2 value exceeds MAXLENGTH setting 35..

This was a fingerprinting attack. If the attacker retrieved a page, then the input mechanism would allow sanitized input. If the attacker received an error message, then perhaps important information about the server configuration would be revealed.


Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and char(124)+user+char(124)=0 value exceeds MAXLENGTH setting 35..

This string evaluates to FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and |user|=0.


Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149' and char(124)+user+char(124)=0 and '%'=' value exceeds MAXLENGTH setting 35..

This string evaluates to FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and |user|=0 and ‘%’=’