On 8/1/2011 8:21 AM, Jerry C. Adams wrote:
I hate to sound like an idiot (which I am as regards SQL), but I've read
these statements before about SQL injection and even googled it.
What I don't understand is why building the SELECT string with '+
UserEnteredValue' is more liable to injection that using parameters. That
is, couldn't the parameters be compromised as well?
Just asking.
Nothing idiotic about it. As people have been trying to explain (but
it's difficult!), the issue has to do with how the statement is parsed.
Most specifically, it has to do with the semicolon, which in an
interpreted SQL denotes the end of one statement and the beginning of
another. So look at this:
update mytable set description = "?" where key = "value"
In an interpreted statement, the user can replace the ? with a carefully
crafted set of character that looks like this:
"; drop table myTable;
The final SQL statement to be executed will look like this:
update mytable set description = ""; drop table myTable;" where key =
"value"
The first double-quote in the substituted value closes the quoted
string, and thus the semicolon is treated as a statement break. This
will execute the statement { update mytable set description = "" }
(which will incidentally update every row because there's no where
clause), then execute the statement { drop table myTable } which will
delete myTable, and then attempt to execute { " where key = "value" }
which will fail, but the damage has already been done.
With a prepared statement with parameters, the ? is replaced with an
escaped version of the string, and looks like this (effectively):
update mytable set description = "\"; drop table myTable;" where key =
"value"
Notice the backslash? This means the double-quote does NOT close the
string and so the semicolon is not treated as a statement break and the
statement executes as YOU intended, not as the hacker intended. Of
course, the description field will have some nonsense in it, but that's
okay, the SQL is not executing arbitrary code.
A simple way around this, BTW, is to code you own routine that escapes
the data entered by the user (that is, checks for double-quotes and puts
the backslash in), and then use the escaped data in your concatenation,
but that's not always practical and the problem is avoided with prepared
statements.
On the other hand, dynamic SQL statements can be too complex for
prepared statements, and that's when the escaping mechanism can be useful.
Hope this helps a little.
Joe
midrange.com
