Anatomy of a SQL Injection Attack

Trailrunner7 writes "SQL injection has become perhaps the most widely used technique for compromising Web applications, thanks to both its relative simplicity and high success rate. It's not often that outsiders get a look at the way these attacks work, but a well-known researcher is providing just that. Rafal Los showed a skeptical group of executives just how quickly he could compromise one of their sites using SQL injection, and in the process found that the site had already been hacked and was serving the Zeus Trojan to visitors." Los's original blog post has more and better illustrations, too.

Comments

Re:Use a persistence library

[Splab]

One should use positional/named bindings and let the driver handle escape sequences, make sure the Web user only has access to what is needed, rather than running everything as root. Use procedures/views where possible and never allow dynamically created queries.

Re:Use a persistence library

[SpazmodeusG]

You still need bind variables mentioned by the gp if using HQL.
http://www.owasp.org/index.php/Interpreter_Injection#ORM_Injection

It is a sad world we live in.

[TaggartAleslayer]

I go through this all of the time. Though I call it laziness, it is actually a combination of ignorance, indignation, and laziness.

Here is a very, very, very simple and very, very, very standard way of keeping SQL injections out. Validate everything at every level. There you go. Done.

1) Client side matters. Check input, validate it and pass it through to the application layer.
2) Application layer matters. Check variable, strictly type it, validate it and pass it through to your data layer.
3) Data layer matters. Check argument against strict type, validate it, paramaterize it, and pass it off to the database.
4) Database matters. Check paramater against strict type, validate it, and run it.

You run into problems when someone only follows any one of the steps above. You could handle it with a medium level of confidence in areas 2 and 3 (and if you're asking why not 1 and 4, go sit in the corner while the grown-ups talk), but good practice for keeping it clean is validate it at every layer. That doesn't mean every time you touch the information you have to recheck the input, but every time it moves from one core area of the platform to another or hits an area it could be compromised, you do.

As I said above, the only reason for not following 1-4 is laziness, ignorance, or indignation. SQL injections aren't hard to keep out.

We're in an age where web development IS enterprise level programming and developers need to treat it as such.

There, I just saved your organization millions of dollars. Go get a raise on my behalf or something.

Re:Lemme be the first to say

[ztransform]

It was unstable in a sense that variables sometimes got strange values inexplicably.

Perl doesn't stop you from programming like a rodeo clown (for those who don't even qualify as cowboys...).

If you're going to make zealous use of globals and then use mod_perl you will get hurt.

Universities teach about something called "coupling". Every professional programmer will talk about something called "use strict". If either of these concepts are too difficult you're better off with a language that does its best to help you from yourself (but be aware Java threads are not going to stop any determined doofus from causing real pain).

Re:Use a persistence library

[CastrTroy]

It's ok to create dynamic queries as long as you aren't generating those based on user content. Doing the following (VB/Pseudocode) is perfectly fine.

sql = "SELECT item FROM table WHERE keyword IN ("
FirstValue = True
ParamNo = 1

For each Value in MyValueList
If Not FirstValue Then
sql &= ","
Else
FirstValue = False
End If

sql &= "@Param_" & i
cmd.Parameters.AddWithValue("@Param_" & i,Value)
ParamNo += 1
Next

sql &= ")"

Since there is no user input used in generating the query, you can never have an SQL inection attack, and still use dynamic queries. There are ways to do dynamic queries, without opening yourself up to attacks.

Re:Use a persistence library

[Qzukk]

What if "item"s came from the users in the first place? Most databases don't return the strings pre-escaped for reuse in the database.

Personally, web programming is where "Hungarian Notation" style variable names shine: I have htVariableName, dbVariableName and the original inVariableName, and it's blindingly obvious when I'm using the wrongly-escaped string in the wrong place or re-escaping something I already escaped.