This is a Rant
Edit: see end of post
Linux.com :: Protecting your MySQL database from SQL injection attacks with GreenSQL
This is an article (written by a very clueless person) about GreenSQL which is a tool (written by a very clueless person) that acts as a proxy between an application and a MySQL database which attempts to detect malicious, likely-injected SQL statements.
Do not interpolate strings into your SQL statements.
Then, there are all the hilariously dreadful comments.
"better yet, encode the bloody data before you shove it in there" --AnonymousDo not interpolate strings into your SQL statements.
"Do you honestly think that anybody who doesn't know how to use simple, foolproof SQL-quoting functions is really going to be able to figure out how to correctly set up a package like this?" --AnonymousDo not interpolate strings into your SQL statements.
"Why don't you try to actually learn to secure your code instead of being a lazy (or completely unskilled) administrator? Surely mysql_real_escape_string() isn't too hard to incorporate?" --AnonymousDo not interpolate strings into your SQL statements.
Look, it's simple. Most database interfaces have a function called 'execute' or similar which takes two arguments: a string of SQL with markers like '?' in it, and then a tuple of arguments to be used as the values of those markers.
execute('SELECT * FROM users WHERE name = ? AND email = ?', ("radix", "radix@twistedmatrix.com"))Do that. Don't do any of the following:execute('SELECT * FROM users WHERE name = %s and email = %s' % ("radix", "radix@twistedmatrix.com"))name = "radix";
email = "radix@twistedmatrix.com";
execute('SELECT * FROM users WHERE name = $name and email = $email');
String escaping is an absolutely retarded alternative to this. Why would you bother escaping or "encoding" your strings when you can simply use the database API as it was intended, without interpolating strings?
Edit: This concept of passing parameters has nothing at all to do with the "prepared statements" feature of popular databases. This is a much simpler feature. This is not a new feature. This feature is commonly called "bind parameters", and it has been around for decades.
Why do so few people know about this?
7 comments:
Just FIY, even if using ? marks for positional arguments, there exist cases where SQL injection is still possible: if a pl/SQL function contains a dynamic concatenation of a statement that can't be done otherwise(PostgreSQL temp table case), then quoting is necessary. Pg implements quote_literal function for this, see here for more.
You show many examples of what not to do. How about showing a short example of what TO do?
I agree with the sentiment of this post; however, I would also note that it's not always possible to use placeholders. Specifically, there are many cases where people want to change other aspects of the query using user input (e.g. the order by column, the LIMIT or OFFSET for the query). In these other cases properly filtering data is important as they can also be exploited for SQL injection.
@David: Perhaps the formatting of the post made it unclear (I hate blogger) or maybe my wording was unclear, but the first example is something "TO do".
execute('SELECT * from users WHERE name = ? AND email = ?', ("radix", "radix@twistedmatrix.com"))
> Why do so few people know about this.
Here's my theory: string interpolation is a big hammer.
* It gets you going quickly with the absolute minimum mental baggage.
* It allows you to do anything, most of which are bad ideas unless you are writing an ORM.
* It is congruent with the mental model of "sql as a command line", it is perceived as "simple".
The parameter syntax of the execute command requires a mental leap that people are not initially driven to make. And afterward they just keep unraveling the consequences of the wrong initial assumption. And the more they have invested in dealing with sql interpolation, they less likely they are to step back and see it was all in vain.
People often tend not to be very rational.
It's not just for security. Bind parameters also allow a statement that's executed many times with different parameters to take advantage of a cached query plan, reducing the overhead of subsequent executions and also reducing the total set of statements filling up the cache. Oracle in particular is sensitive to this.
Once again, I'll blame PHP. When it is so easy to toss variables inside string literals, it becomes way too easy to do things like "select * from $tblname" and from there, the shit doesn't just hit the fan, it sprays into the face of a nun.
Post a Comment