4 ms·

This is all quite clever, but any wary developer makes sure to use either properly sanitized inputs, or better still, prepared SQL statements (as f.e. available

This is all quite clever, but any wary developer makes sure to use either properly sanitized inputs, or better still, prepared SQL statements (as f.e. available through PHP's PDO method), so this trickery, no matter how clever, is really not a big issue.

tptacek 16y ago
"Any wary developer" ranks up there with "a sufficiently clever compiler" among the True Scotsmen of our industry. I don't think you're right; empirically, unsafe SQL is a very big issue, and the places where a QA team is unlikely to stumble across injection vectors (ie, any vector where the string "O'Malley" doesn't pop up an error to paste into a ticket) are worse still, since teams don't find them.
- frederique 16y ago
  i think you don't look outside your own "boxeola" too much. no all development is done by a) clueless team that passes the software on to b) quality assurance + bughunters. relying on or assuming that someone else will take care of eventual loose ends is a very bad and ignorant approach to creating software, and in my soon 15 years of experience as a european programmer this is thankfully no where near the majority of cases.
  - daeken 16y ago
    I think one key difference is in when the software was developed. If I had to guess, most software developed in the past 5 years (being conservative here) is most likely not vulnerable to SQL injection, simply because doing things the right way (that is, using parameterized queries and the like) is so easy these days. But if you look at software developed prior -- and software built at a lower level, e.g. directly hitting the PHP mysql API -- you've got a fair shot at stumbling upon SQL injection, I'd say. That means that most developers writing say, Django or Rails apps, are never going to have to think about SQL injection, and won't see how prevalent it is. In theory, SQL injection is a solved problem -- we've known how to avoid it for a long, long time -- but in practice, it's still out there in full force, largely due to legacy code.
    - tptacek 16y ago
      Parameterized queries are also simply not the panacea they're made out to be; there remain plenty of opportunities for injection even with a parameterized query. SQL injection via MD5 digests are indeed unlikely, but not because of query structure --- rather, because most developers don't know to take the binary result instead of the more convenient hex result. It just rubs me the wrong way when people claim "if you do things right, you'll never run into this problem anyways". Two possible interpretations: either "do things right" is too broad to mean anything (no-true-Scotsman-style) or "do things right" involves a piece of advice like "used parameterized queries" which doesn't actually work reliably in the real world. We have absolutely found SQL injection in Rails code before. I've also had engagements on very modern financial codebases where the developers were able to expound at length on how impossible it would be for them to have injection --- providing entirely sane design rules to back it up --- that ended up losing their entire app to pre-auth SQLI. There's always some tiny corner of the app --- a custom query builder, a hand-hacked pagination system, a sort column generator, a table selector parameter, that one stored procedure that does dynamic SQL and doesn't know what U+2032 is --- that manages to slip up.
      - jtdowney 16y ago
        > Parameterized queries are also simply not the panacea they're made out to be; there remain plenty of opportunities for injection even with a parameterized query. Can you describe a case or point to example code using a parameterized query that is vulnerable to SQL injection? I've seen a stored procedure that built raw queries and pass them to sp_executesql (T-SQL) that provided a vector for SQL injection. However I am struggling to think of a case where a parameterized query could allow for SQL injection.
        tptacek 16y ago
        Not every input to a query can be bound as a parameter. Simplest example: User.find_by_sql("SELECT * from users where name = ? LIMIT #{ limit }", name) Other examples: sort order (ASC/DESC), table selection, join columns, GROUP BY argument. If you think I'm arguing against parameterized queries: of course not. Use them. But know their limitations.
  - tptacek 16y ago
    We're basically paid to look in other people's boxeolae. Sorry, if your own personal experience suggests that most software is rigorous with SQL and input validation, I think I have to assert a broader and more accurate perspective on the issue. It feels statistically improbable to me that we're just missing all the "good" software.
    - deleted 16y ago
      [deleted]
      - deleted 16y ago
        [deleted]
        daeken 16y ago
        Sorry, I actually intended to reply to the parent of your comment -- reposted and deleted my response just after you commented. Parameterized queries aren't a panacea, but they seem to mitigate most of the issues. That said, a lot of people rely solely on them, thinking they really are. While certain things make the vast majority of attacks infeasible (e.g. using SQLAlchemy rather than any straight SQL), nothing can replace actually knowing about the security issues and designing solutions that are immune to them.
    - frederique 16y ago
      this could be a case of demographical difference. programming culture and methods of approach is vastly different in europe, the americas, and asia as indeed often told by migrating workers in our field. consequently we can assert that it is equally likely that it is just you who have a narrow and selfserving perspective of your business, and that there is more diversity out there than what you specifically deal with for your living :-)

tptacek 16y ago

"Any wary developer" ranks up there with "a sufficiently clever compiler" among the True Scotsmen of our industry. I don't think you're right; empirically, unsafe SQL is a very big issue, and the places where a QA team is unlikely to stumble across injection vectors (ie, any vector where the string "O'Malley" doesn't pop up an error to paste into a ticket) are worse still, since teams don't find them.

frederique 16y ago
i think you don't look outside your own "boxeola" too much. no all development is done by a) clueless team that passes the software on to b) quality assurance + bughunters. relying on or assuming that someone else will take care of eventual loose ends is a very bad and ignorant approach to creating software, and in my soon 15 years of experience as a european programmer this is thankfully no where near the majority of cases.
- daeken 16y ago
  I think one key difference is in when the software was developed. If I had to guess, most software developed in the past 5 years (being conservative here) is most likely not vulnerable to SQL injection, simply because doing things the right way (that is, using parameterized queries and the like) is so easy these days. But if you look at software developed prior -- and software built at a lower level, e.g. directly hitting the PHP mysql API -- you've got a fair shot at stumbling upon SQL injection, I'd say. That means that most developers writing say, Django or Rails apps, are never going to have to think about SQL injection, and won't see how prevalent it is. In theory, SQL injection is a solved problem -- we've known how to avoid it for a long, long time -- but in practice, it's still out there in full force, largely due to legacy code.
  - tptacek 16y ago
    Parameterized queries are also simply not the panacea they're made out to be; there remain plenty of opportunities for injection even with a parameterized query. SQL injection via MD5 digests are indeed unlikely, but not because of query structure --- rather, because most developers don't know to take the binary result instead of the more convenient hex result. It just rubs me the wrong way when people claim "if you do things right, you'll never run into this problem anyways". Two possible interpretations: either "do things right" is too broad to mean anything (no-true-Scotsman-style) or "do things right" involves a piece of advice like "used parameterized queries" which doesn't actually work reliably in the real world. We have absolutely found SQL injection in Rails code before. I've also had engagements on very modern financial codebases where the developers were able to expound at length on how impossible it would be for them to have injection --- providing entirely sane design rules to back it up --- that ended up losing their entire app to pre-auth SQLI. There's always some tiny corner of the app --- a custom query builder, a hand-hacked pagination system, a sort column generator, a table selector parameter, that one stored procedure that does dynamic SQL and doesn't know what U+2032 is --- that manages to slip up.
    - jtdowney 16y ago
      > Parameterized queries are also simply not the panacea they're made out to be; there remain plenty of opportunities for injection even with a parameterized query. Can you describe a case or point to example code using a parameterized query that is vulnerable to SQL injection? I've seen a stored procedure that built raw queries and pass them to sp_executesql (T-SQL) that provided a vector for SQL injection. However I am struggling to think of a case where a parameterized query could allow for SQL injection.
      - tptacek 16y ago
        Not every input to a query can be bound as a parameter. Simplest example: User.find_by_sql("SELECT * from users where name = ? LIMIT #{ limit }", name) Other examples: sort order (ASC/DESC), table selection, join columns, GROUP BY argument. If you think I'm arguing against parameterized queries: of course not. Use them. But know their limitations.
- tptacek 16y ago
  We're basically paid to look in other people's boxeolae. Sorry, if your own personal experience suggests that most software is rigorous with SQL and input validation, I think I have to assert a broader and more accurate perspective on the issue. It feels statistically improbable to me that we're just missing all the "good" software.
  - deleted 16y ago
    [deleted]
    - deleted 16y ago
      [deleted]
      - daeken 16y ago
        Sorry, I actually intended to reply to the parent of your comment -- reposted and deleted my response just after you commented. Parameterized queries aren't a panacea, but they seem to mitigate most of the issues. That said, a lot of people rely solely on them, thinking they really are. While certain things make the vast majority of attacks infeasible (e.g. using SQLAlchemy rather than any straight SQL), nothing can replace actually knowing about the security issues and designing solutions that are immune to them.
  - frederique 16y ago
    this could be a case of demographical difference. programming culture and methods of approach is vastly different in europe, the americas, and asia as indeed often told by migrating workers in our field. consequently we can assert that it is equally likely that it is just you who have a narrow and selfserving perspective of your business, and that there is more diversity out there than what you specifically deal with for your living :-)