5 ms·

The database in this case has to share some of the blame. For years MySQL did not support parametrized queries or stored procedures (or did not promote them), a

by kprobst 16y ago

The database in this case has to share some of the blame. For years MySQL did not support parametrized queries or stored procedures (or did not promote them), and many of the people coding against it consistently claimed that they didn't need that kind of "enterprise crap" polluting their favorite DB. This gave way to a MASSIVE code base that is inherently vulnerable to injection attacks. So yeah, it's the fault of the developers but it's also the fault of the people who aggressively marketed and evangelized MySQL and helped create the ditch they're just now digging themselves out of. It's a bit like the VB/Access culture Microsoft promoted back in the day, which generated some of the most hideous corporate apps I've ever seen. Yes, some developers are bad, but the company or group doing the evangelizing/marketing also needs to share some responsibility.

FooBarWidget 16y ago
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: BlogComment.find(:all, :conditions => ['email = ?', 'foo@bar.com']) 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: sql_exec("CALL InsertBlogComment('" + get_parameter("text") + "')")
- awj 16y ago
  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.
  - tptacek 16y ago
    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.
    - nupark 16y ago
      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.
      - tptacek 16y ago
        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.
        FooBarWidget 16y ago
        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.
- nupark 16y ago
  > 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: ActiveRecord's use of the non-parameterized APIs has led to a number of escaping/injection issues in the past eg, (http://www.ruby-forum.com/topic/152058 http://www.ruby-forum.com/topic/152058, http://gsa.ca.com/vulninfo/vuln.aspx?id=36929 http://gsa.ca.com/vulninfo/vuln.aspx?id=36929, etc). > 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.
  - tptacek 16y ago
    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.
    - nupark 16y ago
      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.
- jschrf 16y ago
  "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.
  - tptacek 16y ago
    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.
    - jschrf 16y ago
      Can you elaborate on this? Given a properly parameterized query, where none of the parameters are ever evaluated, how do any user inputs remain unseperated from query structure?
      - nbpoole 16y ago
        From elsewhere (with context): http://news.ycombinator.com/item?id=2375985 http://news.ycombinator.com/item?id=2375985
        jschrf 16y ago
        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.
        16y ago

by kprobst 16y ago

FooBarWidget 16y ago

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: BlogComment.find(:all, :conditions => ['email = ?', 'foo@bar.com']) 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: sql_exec("CALL InsertBlogComment('" + get_parameter("text") + "')")

awj 16y ago
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.
- tptacek 16y ago
  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.
  - nupark 16y ago
    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.
    - tptacek 16y ago
      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.
      - FooBarWidget 16y ago
        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.
nupark 16y ago
> 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: ActiveRecord's use of the non-parameterized APIs has led to a number of escaping/injection issues in the past eg, (http://www.ruby-forum.com/topic/152058 http://www.ruby-forum.com/topic/152058, http://gsa.ca.com/vulninfo/vuln.aspx?id=36929 http://gsa.ca.com/vulninfo/vuln.aspx?id=36929, etc). > 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.
- tptacek 16y ago
  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.
  - nupark 16y ago
    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.
jschrf 16y ago
"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.
- tptacek 16y ago
  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.
  - jschrf 16y ago
    Can you elaborate on this? Given a properly parameterized query, where none of the parameters are ever evaluated, how do any user inputs remain unseperated from query structure?
    - nbpoole 16y ago
      From elsewhere (with context): http://news.ycombinator.com/item?id=2375985 http://news.ycombinator.com/item?id=2375985
      - jschrf 16y ago
        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.
        16y ago