You don't need parameterized queries or stored procedures for protection against injection. In fact they're not injection protection mechanisms at all. What you actually mean is that the database libraries should provide parameterized-style APIs. There's no need for the database's native wire protocol to explicitly support parameterization to implement this. For example Ruby on Rails's ActiveRecord provides an API that looks a lot like it uses parameterization under the hood:
But if you look at the source code you'll see that it just constructs a normal SQL query by internally substituting the '?' with the escaped version of 'foo@bar.com'. It does not utilize the database parameterization APIs at all.
If you think stored procedures protect you against SQL injection, consider the following code snippet:
> You don't need parameterized queries or stored procedures for protection against injection ... There's no need for the database's native wire protocol to explicitly support parameterization to implement this.
This means that every protocol client must implement their own query escaping, rather than relying on the database to provide a single, normative implementation of escaping.
> For example Ruby on Rails's ActiveRecord provides an API that looks a lot like it uses parameterization under the hood:
> If you think stored procedures protect you against SQL injection, consider the following code snippet ...
I believe the original poster was referring to the use of stored procedures as a mechanism for preventing or discouraging unintended direct modification of the database by applications, rather than SQL injection, specifically.
It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.
The examples you've provided of Rails SQLI issues are bad ones:
* The first is a discussion of SQLI in an interface designed to accept raw SQL; it's the ActiveRecord "back door" interface.
* The second is a discussion of SQLI in a context where parameterized queries don't work anyways (the MySQL protocol doesn't accept LIMIT and OFFSET arguments as anything but integer constants); it is also the simplest of the class of SQLI concern areas (you can solve it by blindly calling #to_i on your inputs), which also includes table names, sort orders, and column references.
It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.
The database is the normative reference on what is and is not a special cased character and how escaping should be implemented. I don't think it's reasonable to assert that escaping is a concern that should be adopted by every framework that might ever talk to a database.
The examples you've provided of Rails SQLI issues are bad ones
I'm sure you could supply some better examples since your focus is in security, and there are a vast number of issues that have arisen in ActiveRecord's escaping (especially in early versions of Rails). These are merely what I quickly found while Googling.
The first is a discussion of SQLI in an interface designed to accept raw SQL; it's the ActiveRecord "back door" interface.
It's also an interface that was repeatedly and unintentionally used by Rails users to insert unescaped queries in a way that did not immediately appear incorrect, as evidenced by the preponderance of questions on the subject.
The second is a discussion of SQLI in a context where parameterized queries don't work anyways (the MySQL protocol doesn't accept LIMIT and OFFSET arguments as anything but integer constants); it is also the simplest of the class of SQLI concern areas (you can solve it by blindly calling #to_i on your inputs), which also includes table names, sort orders, and column references.
Given that this conversation is occurring in the context of discussing how MySQL's design has led to exactly these types of errors, I think this is an applicable example.
I don't know how to respond to any of these points.
The discussion at hand is, "who's job is it to defend against SQLI, the database, the framework, or the application?".
Of those three components, the database is least well equipped to defend against SQLI. SQLI is "avenue for attacker to submit queries to a database contrary to the intentions of the application". The database's simple job is to accept and execute queries.
... and spent a good hour reading the Rails source code. My take away was that, historically, a lack of care coupled with a lack of use of the parameterized APIs left the door open to repeated failures in the implementation to protect against SQL injection.
Things have improved in Rails, but as a historical example of the pitfalls of ignoring/eschewing parameterized query APIs, I believe it to be quite valid.
The discussion at hand is, "who's job is it to defend against SQLI, the database, the framework, or the application?". Of those three components, the database is least well equipped to defend against SQLI. SQLI is "avenue for attacker to submit queries to a database contrary to the intentions of the application". The database's simple job is to accept and execute queries.
No, you're reframing the discussion. The original poster commented on MySQL's longtime begrudging support for parameterized queries (or lack thereof) as one of the major causes of the prevalence of SQL injection issues.
It seems clear to me that the database -- as the central implementation responsible for parsing queries -- is the best equipped to provide safe, correct string interpolation of those queries.
People that don't know Rails are going to think that you're making pointed critiques of the framework, when in fact you appear to be repeatedly citing examples of people going through extra effort to use the interfaces Rails provides for directly using SQL instead of ActiveRecord's finders.
I'm not offering a "pointed critique of Rails" ([edit] although your framing it as such seems to encourage downvotes into the negatives). I'm demonstrating how the use of non-parameterized queries source of error that has repeatedly resulted in SQL injection issues in ActiveRecord. I have provided three different examples:
- In the first, the API design itself made it non-obvious/easy to directly concatenate strings while -- at a glance -- appearing to be correct.
- In the second, the API's implementation resulted in SQL injection because :limit and :offset arguments were not correctly escaped.
- In the third, the API's documentation and recommended usage (in 2004) encouraged users to use constructs that appeared to provide defense against SQL injection, but in reality, did not.
> It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.
Sure, but it is unreasonable to assert that SQLI defense is not also a database issue.
Using the provided apis in a way that prevents SQLI is a framework concern. Providing apis that make that possible without re-implementing basic things like escaping is a database concern, otherwise you're just asking people to re-solve the same problems so they each get a chance to screw it up.
The SQL Injection vulnerability is, "user coerces application into submitting an unexpected and unauthorized query".
Blaming the database for that is like blaming the filesystem for pathname injection vulnerabilities.
It could, after all, send a Unix signal to a calling process when a filename contained "..", and demand that the process re-assert it's desire to really reference a different directory.
We may be spiraling here. Parameterized queries are a good thing. I'm glad MySQL has them. I'm not, however, going to wag a finger at MySQL every time someone finds an SQLI vulnerability in an app that uses MySQL, just because 6-7 years ago they didn't have parameterized queries. For one thing, it's not a useful comment (do you want them to implement parameterized queries... again?); for another, it's not particularly valid architectural point; and finally, it's really boring.
I think we definitely are spiraling. My argument is about how the stance MySQL took on parameterized queries has affected the product's community. I don't disagree with you on the technical issues. Well, except that providing useful tools to handle SQL defense correctly is a database issue. And pathnames are a read herring, as they are not a transport for data manipulation commands.
I guess part of it is that I'm just done watching various problems with MySQL (transaction support, parameterized queries, subqueries, bizarro query optimization) be given a pass as "not quite MySQL's problem". Maybe I'm just taking that out on this thread.
Not comparable. h() was a one-sized-fits-all problem to quoting things that could occur anywhere in an HTML DOM. #to_i assures that a bit of syntax that can only ever accept integers is in fact given a real integer.
In case we're misunderstanding each other, I'm also saying that the framework should be doing that, not the caller (as was the case with h()).
You're arguing against the periphery of his point. By waiting so long to provide parameterized queries, MySQL helped foster the attitude that they were "useless enterprise bloat". The lack of parameterized queries was likely an influencing factor in the non-support or non-advertisement of parameterized-style APIs.
MySQL is a crucial part of its own community. You cannot hold the community responsible for this situation while giving MySQL a pass.
I have never heard someone say parameterized queries were "useless enterprise bloat". Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test. Your reaction here sounds hyperbolic, and the parent commenter is right: parameterized queries, while helpful, are neither required nor sufficient for defense against SQLI.
> I have never heard someone say parameterized queries were "useless enterprise bloat".
I have. Usually from people with no idea what they're talking about, maybe one of us has been (un)lucky in our experience. Hopefully it's me.
> Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test.
That's great! How long has it been true, though? When I first learned PHP (2003), this was not mentioned. Maybe it existed, either in MySQL directly or as an api method, but it certainly wasn't widely advertised. Maybe looking all the way back to 2003 is stepping outside the scope of "modern" here, but I still occasionally get to deal with problems created back then.
> Your reaction here sounds hyperbolic, and the parent commenter is right: parameterized queries, while helpful, are neither required nor sufficient for defense against SQLI.
I'm sure it does, considering the differences in our experience of the situation.
Also, my point was not that parameterized queries are the end of SQLI defense or required for it. Just that you can't give MySQL as a company a pass on a situation they helped create. Parameterized queries are a very simple and common first step in learning to deal with SQL injection. Not implementing them for so long did not help the situation in this respect.
Could you expand a bit on how parameterized queries are not sufficient for defense against SQL injection (assuming, of course, that developers use the escaping and do not concatenate unescaped data into queries)?
As for them being required -- you can obviously escape queries yourself, but the normative reference for escaping is the target database itself, and reproducing escaping locally in the client brings with it the likelihood of introducing an error in the custom implementation.
Not every "input" to a "query" (using these terms loosely) can be bound as a variable. Simple example: ASC and DESC. There are trickier examples that are still common.
It is a bad idea for applications to implement quoting regimes, and it is a bad idea for frameworks to try to create one-size-fits-all quoting regimes like PHP used to. That doesn't mean it's a bad idea for a framework's e.g. MySQL support to provide the capability of sanitizing MySQL inputs under a common database API.
The problem with that is that almost nobody uses the official database APIs. The official APIs are usually C libraries (e.g. libmysqlclient) but pretty much everybody uses third party wrappers (e.g. Perl DBI, the mysql/mysql2 gem for Ruby, the PHP default MySQL bindings, etc). Few people program against the database in C or C++. It was and is up to the third party API providers to provide easy sanitization APIs, I don't see how MySQL could have changed that situation by providing such APIs themselves.
> Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test
I'll resist the pot/kettle/colour connection here about plugging your own stuff on HN in a web app security thread. Agh, too late.
On a more serious note, if all the Internet apps Matasano test are using parameterized queries then:
a) Matasano never tests a MySQL-based app
AND
i) Matasano does very little web app testing these days
OR
ii) You're not quite telling us the truth
Seriously, parameterized queries while being a best practice so to speak are not a one size fits all solution and is not something implemented by everyone. The amount of shonky development practices we come across at my unnamed and unplugged company far outweighs the instances of the security aware, certainly on the first, second and sometimes third time round.
Today I'm testing a wordpress-based web app. A beer says that it's not using parameterized queries.
"In fact they're not injection protection mechanisms at all"
What?! Parameterized queries (in any competent implementation) ARE, in fact, injection protection mechanisms. Escaping != Parameterization.
Ruby on Rails is a poor example to use here - it's a trendy web language that (judging by your comment) does not use proper database practices.
Properly implemented parameterized queries offer protection against SQL injection because they seperate data from instruction - none of the data points in a query are executed, ever.
Gluing SQL instruction strings together with data from users is incredibly stupid and you should never, ever do that.
Use a framework that supports proper parameterized queries.
If parameterized queries guaranteed a total separation between user input and query structure, you'd be right. But they don't. They guarantee a separation between some user inputs and query structure.
I'm failing to see how ASC and DESC would apply here, unless someone is simply passing them on directly from a URL or input form into the SQL, which is almost the equivalent of just letting the user type in the SQL directly. Also, the worst thing that can happen to an extension like LIMIT is that more rows show up than expected, but that wouldn't change the context of the original query to return data that wasn't originally intended to be shown. Plus, it would be very difficult to tag on more SQL to a direct LIMIT number being passed in unless the back-end application was totally brain-dead about how it converted strings to integers.
One other thing - extensions like LIMIT can be parameterized also (shameless plug: our product ElevateDB can do it with its RANGE clause). It's a nice way of getting rid of this type of issue, and allows for easier pagination without having to force the database engine to constantly re-prepare the same query over and over again.
IMHO choosing between ASC and DESC isn't exposing an input into the query in the same way that accepting arbitrary text (escaped or not) into the query is, but thanks for clarifying.
And with regard to LIMIT, it's a bad idea to allow the user to pass in any values without validation, even if you restrict it to integers.
Parametrized queries help resolve the most common kinds of SQL injection, all the "yes but..." argue that it's not a blanket instrument that instantly should make you feel safe and no longer think about security/robustness. That's also true. Such a thing doesn't exist anyway.
If you think stored procedures protect you against SQL injection, consider the following code snippet: