Parameterized queries are vastly simpler than screening input, with less chance of error.

Because one is a hopelessly complicated route that basically requires duplicating the entire horribly complex, constantly changing SQL syntax definition in your input module, while the other just works.

There are cons to parameterized queries?

"As I understand, if #1 used properly and all caveats are taken care of, it can be just as effective as #2." – That's what everybody who has ever fallen victim to SQL injection said, too.

There are cons to parameterized queries? Queries statements can not be modified. That means, no dynamic order by's, no dynamic joins or filters, etc... What you gain in security you lose it (sometimes) in flexibility

@Laiv, just because you can't modify it after you create it doesn't mean you can't customize it before you create it. You can make your query as dynamic as you want at runtime, then prepare it, then parameterize it.

"Screening and filtering" is not a solution at all. Escaping input is.

@Bergi It's a step before escaping. Things like using regex to ensure an ID is only integers, for example.

@Izkata Validation of business rules or parsing input to the data type we expect seems to be unrelated to the database backend. Yes, you should do both, but only escaping prevents the SQLI that the question talks about.

Prepared statements are (and have been from the start!) a feature of SQL, it did not evolve as a way to prevent SQL injection, it just happens to be very good at doing that.

@Bergi I'm not talking about business rules, I'm talking about filtering out the characters that could lead to sql injection. It's a big no-no due to how error-prone it is (especially with different charsets), but that's what that statement makes me think of.

2. was there long before the internet, and sql injection with it, was a thing. It's just that many programmers whose only education was "read some tutorials" didn't know about it, and the early mysql php drivers didn't support it, which is why 1. took off. Once people realized there was a problem, 2. was already there.

@KarlBielefeldt. Yes you can. However it brings some boilerplate code and complexity to your data access. The time saved in one side you lose it doing dynamic statements. Here an answer which I'm in agreement. stackoverflow.com/q/15405288/5934037. (Look at the answer comments)

One thing that stood out to me is that option 1 includes the word "carefully" and option 2 doesn't.

@Izkata If you filter out characters then how do you store arbitrary text? You can't just remove all quotes, etc. from everybody's input. (Hell, this comment contains two quotation marks.)

@Solomonoff'sSecret Just because it's bad doesn't mean it doesn't happen.

The only thing that you should be asking your database to run is code that you, and only you, have written. It should not have any taint of outside untrusted data in the SQL code. Keep executable code and the data that the code operates on entirely separate.

Prepared statements / parameterized queries existed as a performance measure. They just happen to be immune to SQL injection attacks. In the early days of web programming, books contained nice simple examples of how to write SQL queries. For the sake of simplicity in presentation, they demonstrated the literal string approach. Unfortunately, such examples formed the basis of a lot of production code. Only as web programming newbies worked their way up the learning curve did they realize the vulnerability of their code to SQL injection.

Bear in mind: screening and filtering only protects you from the threats you already know about; it does nothing to protect from the threats you haven't thought of. Parameterized queries are a pretty good defense against any injection threat, whether you know about it or not.

You said it yourself, in #1 you said carefully, in #2 no need to be "carefull" just apply prepare statements/parametrized query and you're fine.

Although prepared statements are certainly a vital part of SQLI prevention, it's misleading and dangerous to say things along the lines of "they just work" or "are immune to SQLI" etc. PSs just make injection take more effort. See f.ex. point 1 here or FindBugs' SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING: ..SQL injection could be used to make the prepared statement do something unexpected and undesirable. Let's not suggest that just because we use PSs we are safe from SQLI.

You should neither repeat yourself nor reinvent the wheel, and #1 virtually requires both.

Because this assumption "As I understand, if #1 used properly and all caveats are taken care of..." is inherently unfounded. If we were only talking about old ASCII, then maybe you could do this. But for UNICODE, UTF-8, UTF-16, etc., I don't know even know of any comprehensive list of all of the characters that might be treated as quote/unquote characters, let alone how to insure that they aren't escaped. The only way that #1 could be done effectively is if we used SQL parse trees instead of SQL text commands.

@RBarryYoung, I don't think that anything besides the standard quote characters get treated as quotes for the purpose of SQL parsing.

@WinstonEwert That's also a possible vector of attack for code that handles unicode improperly. Unsurprisingly, PHP had this bug for a long time, and Javascript still has it today. At least I think that PHP finally fixed it, though I'm not entirely sure. Unicode is very complex, and unicode parsers are just as complex, and quite likely to have errors.

@Luaan, can you point me to documentation for this improper unicode handling in javascript? (In particular as it partains to the sql injection issue)

@WinstonEwert mathiasbynens.be/notes/javascript-unicode for example. It's not specifically targetting the security implications, but it's a nice intro into how complicated Unicode is.

@Luaan, I'm well aware that unicode is complicated. But the particular issue I commented on was fear that some unexpected unicode character might be interpreted as a quote, which seems quite dubious to me.

@WinstonEwert Definitely not true. Even in English the characters that will be accepted as quotes in SQL varies considerably with the DBMS product.

Because that's what makes the most sense - separating the data from the query. I'd love to see something like this in HTML actually.

Parameterized queries arguably came before SQL Injection was recognized as a "thing"... often data types needed to be massaged or escaped to reliably get the data in or out of the database. The fact that it intrinsically protects against SQL injection is just another good reason to use them.

@RBarryYoung, can you give me a reference to a SQL product that accepts a quote character not in ascii?

@WinstonEwert The simplest case is when the database understands Unicode, but your client doesn't. When you try to do something like replace("'", "''"), what will happen is that one of the characters isn't actually a quote (since you changed just two bytes instead of four) - so instead of '', you get something like ř', which is weird but valid, and your application is wide open to SQL injection. I'm not saying this exact approach would work in Javascript (it did work in PHP), just noting that even today, the general support for Unicode is quite flawed, and could cause similar issues.

@Luaan, I understand now. Thanks.

@WinstonEwert See here for more examples of encoding-based issues

@JörgWMittag I thought most people who've fallen prey to SQL injection had simply not heard of it.

One more point--not only are parameterized queries easier than defense code, they're easier than dealing with non-hostile dangerous characters. (Say, you hire Mr. O'Neill.)

@immibis For most, probably yes. But some of those found out about SQL injection (sometimes by being exploited), and had the bright idea "I know how to solve this! I'll just replace apostrophes with two apostrophes!" I've done an audit on a bank (at least not a large bank, ugh) where the developers simply didn't understand this. It took me about five minutes to exploit the weakness to read sensitive customer data; that made them take it a bit more seriously :) And yes, PHP was involved, so there were plenty of vectors for the attack :)

Can someone explain what the obsession is with escaping SQL? We have a solution that's pretty much rock-solid and a solution that has failed again and again and again. What's the controversy? Use parameterized queries and find a real problem to solve.

@KarlBielefeldt In SQL that is both parameterized and dynamically generated, getting the order of the ? placeholders out of sync between the statement and the array of input parameters can be as bad as SQL injection if it causes a sensitive input to be treated as not sensitive.

This is a loaded question, since parametric queries were motivated by performance.