

Advanced MySql Exploitation - jacquesm
http://www.blackhat.com/presentations/bh-usa-09/DZULFAKAR/BHUSA09-Dzulfakar-MySQLExploit-PAPER.pdf

======
tptacek
There are three ideas in this paper that may be news to you:

* in MySQL, you can "SELECT 0x414243" and get "ABC"

* you can append "INTO DUMPFILE foo" to a query and MySQL will write the results to the filesystem.

* MySQL won't let you tack an injected statement to the end of another query, but SQL UNION clauses will allow you to add arbitrary SELECTs to a victim query.

Combine these ideas and you can turn SQL injection on some (rare) server
configurations into code execution.

------
ratsbane
I had to think about this paper a bit. While it does describe some interesting
ideas, nothing mentioned here will get past properly escaping input. That is,
if you use DBI's quote method or parameterized queries in Perl or PHP's
mysql_real_escape_string function or whatever is appropriate in your language
and MySQL library then you'll be okay.

Does that seem correct?

~~~
tptacek
No. "Properly quoting" inputs is the worst way to mitigate SQL injection
flaws.

What you need to be doing is replacing your concatenated string queries with
precompiled parameterized queries, where the values in your queries are
replaced with placeholder strings (like "?" and ":varname"), and the values
are passed in a seperate call. Every mainstream database API supports these.

~~~
ratsbane
I didn't say "properly quoting." I said "properly escaping" and mentioned the
DBI quote method E.g. $safestring=$myDBIobject->quote($taintedstring)

From the O'Reilly book "Programming the Perl DBI"

"By far the most important utility method is quote(), which correctly quotes
and escapes SQL statements in a way that is suitable for a given database
engine."

If you're going to reuse a query with different values then it makes more
sense to use parameterized queries. If you're going to reuse an input string
in multiple queries which don't themselves get reused then it makes more sense
to use an escape function like quote.

If you're using a method like DBI's "selectrow_array" which combines the
"prepare," "execute," and "fetchrow_array" steps into one method call then you
pretty much have to use the quote method.

~~~
tptacek
Yes. That O'Reilly book is giving extremely bad advice. You should work from
the premise that there is no safe concatenated string query (regardless of the
corner cases where that isn't true).

~~~
ratsbane
That is absurd.

~~~
tptacek
Here's what's happening here, Douglas. You're thinking, "I know how to quote
my queries so they aren't injectable. I don't need to parameterize them."

Let's stipulate that you're right.

You are not the only programmer on your project --- at least not if your code
means anything. Someone else is going to either (1) extend or (2) cargo-cult
your query. And when they do that, one of two things is going to happen:

* They're simply going to forget to quote properly. For instance, they're going to add an ORDER BY clause, and they're going to accept "ASC" and "DESC" as input from their jQuery auto-loading Ajax table widget, and you're boned.

* They're going to quote using an unsafe quoting function, for instance with regular expressions, and they're going to miss truncation and character set attacks.

I can tell from your "that is absurd" comment that _you_ aren't going to be
sold on any of this. That comment is also a pretty big tip-off that you're not
particularly experienced --- and the only reason I say that is, _I have never
worked a pentest where a client quoted string queries that didn't turn out
injectable_. You might be the _one exceptional app_ in the world that gets it
right, in which case, thank you for proving the rule for me.

People who use prepared statements and who religiously keep user input
completely out of query structure (for instance, by mapping table and column
names, so user input simply can't be put into query structure) don't have to
think about any of these problems. Why anyone would elect _to_ think about
them is beyond me.

~~~
ratsbane
So you're saying...

Properly escaping queries is a bad idea because you might forget to do it and
if you forget to do it then it doesn't work.

Here's a funny thought, though. I remember, many years ago, doing a code
review and noticing something odd. The very smart developer who always used
parameterized queries had written a simple insert into table X. It was all
properly parameterized. Table X, however, had an on insert trigger which
called a stored proc which was about eight pages long. In that stored proc,
two of the parameters were concatenated into a select which was then eval-ed,
thus leading to a very serious injection possibility.

So, in response to your last paragraph, yes, you still have to think about
injection possibility even if you use prepared statements.

Really, that should be the first rule of designing for security: ALWAYS think.

~~~
tptacek
That third paragraph, refering to dynamic SQL inside of a stored procedure,
happens often enough that I thought it made sense to bring it up here several
months ago:

<http://news.ycombinator.com/item?id=824594>

You'd do well to read the rest of the recommendations in that thread as well.

I cannot understand why anyone would opt for concatenated SQL over
parameterized queries when both were available. It seems like simple, ignorant
arrogance. As a contractor for small businesses who won't have their code
reviewed by people like us, developers like you should be _especially_
sensitive to this issue; your customers are going to be screwed over by that
code long after you've moved on.

