3 ms·

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 +

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.
  - jerf 16y ago
    For those who didn't know what tptacek meant by U+2032, like me, a quick Google search picked up this: http://news.ycombinator.net/item?id=1940713 http://news.ycombinator.net/item?id=1940713 , in which he explained it three days ago: "This is a classic web security problem; most famously, WinAPI systems have a 'flattening' function that would convert things like PRIME U+2032 into ASCII 0x27 (the tick that terminates SQL statements)."
- Someone 16y ago
  I am not so sure of that. There is lots of code that is not so much developed as it is evolved from a Q&D n-liner (n<20). Such code can easily have some left-over opportunities for SQL injection attacks.
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 :-)

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.
- jerf 16y ago
  For those who didn't know what tptacek meant by U+2032, like me, a quick Google search picked up this: http://news.ycombinator.net/item?id=1940713 http://news.ycombinator.net/item?id=1940713 , in which he explained it three days ago: "This is a classic web security problem; most famously, WinAPI systems have a 'flattening' function that would convert things like PRIME U+2032 into ASCII 0x27 (the tick that terminates SQL statements)."
Someone 16y ago
I am not so sure of that. There is lots of code that is not so much developed as it is evolved from a Q&D n-liner (n<20). Such code can easily have some left-over opportunities for SQL injection attacks.

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 :-)