News for hackers. Stuff that matters.
Re:A Big Case of Oops by crux_ on Friday February 26, 2010 @11:25AM (#1153429)
Ignore that other person saying to escape the quote -- do as the wikipedia article they linked says, and use parametrized queries. This does all the escaping for you, and unlike escaping, makes it much less likely that something will slip between the cracks.

(There's almost never a good reason to construct SQL on the fly.)

Re:A Big Case of Oops by fendale on Friday February 26, 2010 @12:14PM (#1153562)
Exactly correct - and if you are using Oracle and not using parameterised queries (known as bind variables in the Oracle world), you are quickly going to have performance problems too. That's not the case with MYSQL as it doesn't have a cursor cache in the same way Oracle has. I have no idea about Postgres or SQLServer.

A rule of thumb is that if you are concatenting user input strings into your SQL query strings you are doing it wrong.

Re:A Big Case of Oops by tptacek on Friday February 26, 2010 @01:28PM (#1153752)
Remember that parameterized queries aren't a panacea. There are plenty of things that don't parameterize well, such as table names, sort orders, and limits.
Re:A Big Case of Oops by epochwolf on Friday February 26, 2010 @11:45AM (#1153480)
Parametrized queries still use escaping. The only difference is instead of you doing the escaping and query construction you're letting someone else's code handle it for you. (Code I would assume is far better tested than yours)
Re:A Big Case of Oops by tptacek on Friday February 26, 2010 @01:18PM (#1153719)
This isn't really an accurate description of what's happening in a parameterized query. In most cases, parameterized queries have already been parsed by the time inputs are interpolated into them; if you look at the protocol on the wire, you can see the difference between query structure and slots.

SQL Injection vulnerabilities happen when inputs influence the parsing of the query. You can't do that when the database already has an AST for the query.

Parameterized queries are not just a different kind of quoting.

Re:A Big Case of Oops by crux_ on Friday February 26, 2010 @12:35PM (#1153607)
a) More importantly, they enforce a discipline on you: never treat data as code.

b) I'm guessing there are plenty of databases that execute parametrized queries entirely differently than a plaintext ones, including the absence of an escaping step. Since the whole point of quoting + escaping is to demarcate the "data" parts of a query, and you've already done that with parameters, there's no reason a query engine can't use the parameter directly rather than run it through a useless escape then parse-and-unescape cycle.