13 ms·

Everything you wanted to know about SQL injection (but were afraid to ask)

210 points by ssclafani 13y ago

mrweasel 13y ago
Arguably, I worked with people who didn't know of the concept of "prepared statements", but these are the same kind of people that won't read this article. I think prepared statements should have been highlighted more. ORMs makes things alot simpler, in some cases, and get in your way more often than not. I don't do much .Net anymore, mostly Django stuff, but in both cases the ORMs just forces me to learn a new query syntax. In the end prepared statements and SQL would have been quicker, more flexible... and perhaps safer. I get the concept of the ORM, but I don't really plan to switch database anytime soon and I have needed for the flexibility of having "no ORM". ( Could that be a thing, like No-SQL, No-ORM? ) The funniest SQL injection attacks I've seen have been against our search pages. Attackers assume that search is done as dynamic MySQL queries, that fact that you are actually using Sphinx or Solr seems to escape most wanna be hackers.
- welder 13y ago
  I take the exact opposite view. I use an ORM where you think in SQL (SQLAlchemy) and can build sql queries using functions named the same as the SQL. If you use an ORM then SQLi should be impossible, unless there's a bug in your ORM.
  - crazytony 13y ago
    as you point out you can build the sql query. I've not seen any ORM that validates the SQL you pass it to build the query. This is how SQLi happens in the ORM tier. objects = orm.rawQueryForObjects("select * from people where name = '" + name + "')
    - welder 13y ago
      You build the sql query using chained functions, so it's safe: objects = People.query.filter_by(name=name).all() When I said 'build the sql query' I meant using SQLAlchemy core, which is also safe: objects = select([People]).where(People.name == name)
      - crazytony 13y ago
        Even sqlalchemy has the mechanism I pointed out. I've never seen an ORM not have it because it becomes important if you're putting an ORM on top of a previously designed database or optimising queries. session.query(Object).from_statement("SELECT * ...") http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.from_statement http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalch...
        X-Istence 13y ago
        Sure, and shooting yourself in the foot is also possible by pointing the gun down at the floor ... doesn't mean it is a good idea. Generally the same statement can be built using internal SQLAlchemy... But if you want to do your own, you can in sqlalchemy while still being as safe: http://docs.sqlalchemy.org/en/rel_0_5/sqlexpression.html#using-text http://docs.sqlalchemy.org/en/rel_0_5/sqlexpression.html#usi...
        roc 13y ago
        > "Sure, and shooting yourself in the foot is also possible by pointing the gun down at the floor ... doesn't mean it is a good idea." But it's possible and people do it. Which is, I believe the point was, the counterpoint to "SQLi should be impossible". Sure, One can avoid shooting themselves in the foot with an ORM. But that's also true in SQL.
- zalew 13y ago
  > I get the concept of the ORM, but I don't really plan to switch database anytime soon not sure if you get the concept of ORM, because switching databases is not what ORMs are primarily designed for, it's more of a common feature / side effect that may get invalidated when you use unsupported dbms features. > and I have needed for the flexibility of having "no ORM". http://docs.sqlalchemy.org/en/latest/core/index.html http://docs.sqlalchemy.org/en/latest/core/index.html http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html or a clojure example http://sqlkorma.com/ http://sqlkorma.com/ you can probably find something similar for python, there are a few lightweight sql generation libs. > The funniest SQL injection attacks I've seen have been against our search pages. Attackers assume that search is done as dynamic MySQL queries, that fact that you are actually using Sphinx or Solr seems to escape most wanna be hackers. bots. they see a form, they submit crap to it.
- jcampbell1 13y ago
  Prepared statements don't handle all cases, e.g. "ORDER BY :?" doesn't work.
  - yogo 13y ago
    I think you can add your own kind of preparation/binding on top of the db engine's binding by introducing a little syntax for those cases. Basically what I'm saying is to properly sanitize what gets inserted then perform a string replace on the query string. It comes in handy for other clauses that normal binding does not cover. An example would be you would have your binding take care of something like :foo: as a sort of preprocessing before the db takes care of the others like :foo
  - chiph 13y ago
    Another example (SQL Server specific, perhaps) is parameterizing the COLLATE option. You just can't do it, so you have to string-concat it, which means that your query cache has n versions of each query, where n is the number of locales your app supports. Good thing RAM is cheap.
- dustingetz 13y ago
  anti-ORM is not exactly a new sentiment - ORMs have been a hot & unresolved debate for two decades - but you need to be very careful about what you read on the internet, a lot of people express reasonably written opinions with an authoritative tone (like me) but are just wrong (hopefully not me haha). Find someone smart that you trust, like Rich Hickey or Greg Young, and try to understand what they say.
- twistedpair 13y ago
  Odd that few articles champion a limited database user. In my apps there is a crippled read only user, an update user with limited UPDATE/INSERT privileges, and finally an admin user that is in no way accessible from the app. There is no reason for your standard app user to have access to the core database object tables to do DDL lookups or mess around with the internals. Further, most queries should only run through the RO user. Finally, no user for your app should have DROP or SUPER privileges. You also should disable multi-query requests out the gate. Of course the dev's will need to remember to use the proper set of creds from their favorite SQL IDE, and there will be a few hiccups for 10min until you've got the proper creds set for the app user, but then you can limit the worries from an attack.
jroseattle 13y ago
Nice article, but this is training wheels. Maybe it's inferred, but this article should be pointed at the rookie developer.
- magoon 13y ago
  Which article is for intermediate and advanced?
  - dmix 13y ago
    http://www.amazon.com/SQL-Injection-Attacks-Defense-ebook/dp/B008PQESLI/ http://www.amazon.com/SQL-Injection-Attacks-Defense-ebook/dp...
- glhaynes 13y ago
  I learned long ago to sanitize my inputs/etc so I don't think I'll write more secure code for having read it, but I hadn't ever thought through exactly how an attacker might do things like time-based blind SQL injection. So I found it interesting.
  - jeltz 13y ago
    You should whenever you can always parametrized queries over sanitizing the input. Manually escaping or sanitizing should be the last resort.
    - PavlovsCat 13y ago
      Manually escaping or sanitizing should be the last resort. Why, other than "because one might forget it"? That is, what if bits of the query depend on various conditions and need to be constructed part by part? Keeping the placeholders and the actual values synced seems like a nightmare in those cases.. unless I am missing something.
      - ygra 13y ago
        One could imagine something like public static void AddParameterValue(this SqlCommand cmd, string paramName, object value) { if (cmd.CommandText.Contains("@" + paramName)) cmd.Parameters.AddWithValue(paramName, value); } to help set parameters only if they are referenced in the SQL statement.
sehrope 13y ago
Both raw SQL and using an ORM have their place. The latter is definitely less prone to unintended SQL injections but it's still possible. The reverse is true too. Raw SQL can be quite secure if you're not an idiot about it (golden rule: never build command strings from user input). If you use prepared statements across the board you don't even have to bother with sanitizing user input[1]. The main reason to use an ORM is for programmer efficiency. Sure I can write a bunch of CRUD operations but why bother if I can have the ORM do it for me? Since CRUD operations are either inserting a new row or accessing/updating by a primary key it'll be indexed as well so no perf issues[2]. The additional work by your app code for the ORM library is meaningless compared to the DB round trip anyway. My favorite part? Adding a new field goes straight to the model and nowhere else[3]. The big place for raw SQL is as the secret sauce on the meat and potatoes of your apps. Once you've got a real data set and you want to combine, slice, dice, you're not going to do that through an ORM. You want those queries to be as performant as possible. If you've structured your tables properly (proper foreign keys, normalization, etc) then the writing custom SQL will also be much more straightforward then trying to kludge together ORM commands to do what you want. On top of that, the work will happen on the database where it can filter it prior to your app processing it. For our app[4] most of the CRUD pages are handled by an ORM but there's quite a bit of custom SQL too. One example is for security authorizations. Validating security authorizations (can user X access DB y) is a hierarchical check. The logic is all done in a Postgres stored proc (well technically a function). Doing it via an ORM would inefficient, both in programmer time and computer runtime. [1]: You probably should though. It's generally a good idea to have some kind of white listing for what is an acceptable value for a field. Either way though you need to make sure you escape them when outputing HTML for webapps to prevent XSS. That combined with prepared statements when interacting with user inputs is the only right way to do things. [2]: For basic CRUD operations and even simple one-to-many lists. Beyond that things can and do get hairy but those aren't the majority of cases. The majority of app code is vanilla id-based select, inserts, updates, and deletes. [3]: http://en.wikipedia.org/wiki/Don%27t_repeat_yourself http://en.wikipedia.org/wiki/Don%27t_repeat_yourself [4]: http://www.jackdb.com/ http://www.jackdb.com/
- dustingetz 13y ago
  i think your third paragraph is more or less false for apps over a certain size, think enterprise-y stuff. it is certainly false for the class of apps that i work on - big pharma regulatory compliance software, very complex data model, hundreds of tables etc. Our ORM can express higher level queries and a wider set of queries than raw SQL - you can express a query in one line (one thought) that compiles down to quite a few nested SQL expressions. Finally, the problems of orm stem from the fundamental nature of SQL, so dropping into raw sql couldn't possibly fix them. You need something like Datomic or CQRS/ES to remove the object/relational impedance mismatch at a fundamental level. (This is analogous to why Git rocks compared to SVN; Git solves the problem of centralized mutable state at a fundamental level which opens the door for a better model of the problem and more powerful abstractions.)
AaronBBrown 13y ago
And of course, the highly relevant classic XKCD: http://xkcd.com/327/ http://xkcd.com/327/
jackmaney 13y ago
';DROP TABLE articles;--
- twistedpair 13y ago
  There was a chance it might work.
300bps 13y ago
Anything that teaches developers about security is a good thing including his article. One minor nit - ASP.NET has been mostly shielded from these types of things for 6 or 7 years. He used ASP.NET in his examples which meant he purposely turned off several protections to get the examples to work.

210 points by ssclafani 13y ago

Arguably, I worked with people who didn't know of the concept of "prepared statements", but these are the same kind of people that won't read this article. I think prepared statements should have been highlighted more. ORMs makes things alot simpler, in some cases, and get in your way more often than not. I don't do much .Net anymore, mostly Django stuff, but in both cases the ORMs just forces me to learn a new query syntax. In the end prepared statements and SQL would have been quicker, more flexible... and perhaps safer. I get the concept of the ORM, but I don't really plan to switch database anytime soon and I have needed for the flexibility of having "no ORM". ( Could that be a thing, like No-SQL, No-ORM? ) The funniest SQL injection attacks I've seen have been against our search pages. Attackers assume that search is done as dynamic MySQL queries, that fact that you are actually using Sphinx or Solr seems to escape most wanna be hackers.

welder 13y ago
I take the exact opposite view. I use an ORM where you think in SQL (SQLAlchemy) and can build sql queries using functions named the same as the SQL. If you use an ORM then SQLi should be impossible, unless there's a bug in your ORM.
- crazytony 13y ago
  as you point out you can build the sql query. I've not seen any ORM that validates the SQL you pass it to build the query. This is how SQLi happens in the ORM tier. objects = orm.rawQueryForObjects("select * from people where name = '" + name + "')
  - welder 13y ago
    You build the sql query using chained functions, so it's safe: objects = People.query.filter_by(name=name).all() When I said 'build the sql query' I meant using SQLAlchemy core, which is also safe: objects = select([People]).where(People.name == name)
    - crazytony 13y ago
      Even sqlalchemy has the mechanism I pointed out. I've never seen an ORM not have it because it becomes important if you're putting an ORM on top of a previously designed database or optimising queries. session.query(Object).from_statement("SELECT * ...") http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.from_statement http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalch...
      - X-Istence 13y ago
        Sure, and shooting yourself in the foot is also possible by pointing the gun down at the floor ... doesn't mean it is a good idea. Generally the same statement can be built using internal SQLAlchemy... But if you want to do your own, you can in sqlalchemy while still being as safe: http://docs.sqlalchemy.org/en/rel_0_5/sqlexpression.html#using-text http://docs.sqlalchemy.org/en/rel_0_5/sqlexpression.html#usi...
        roc 13y ago
        > "Sure, and shooting yourself in the foot is also possible by pointing the gun down at the floor ... doesn't mean it is a good idea." But it's possible and people do it. Which is, I believe the point was, the counterpoint to "SQLi should be impossible". Sure, One can avoid shooting themselves in the foot with an ORM. But that's also true in SQL.
zalew 13y ago
> I get the concept of the ORM, but I don't really plan to switch database anytime soon not sure if you get the concept of ORM, because switching databases is not what ORMs are primarily designed for, it's more of a common feature / side effect that may get invalidated when you use unsupported dbms features. > and I have needed for the flexibility of having "no ORM". http://docs.sqlalchemy.org/en/latest/core/index.html http://docs.sqlalchemy.org/en/latest/core/index.html http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html or a clojure example http://sqlkorma.com/ http://sqlkorma.com/ you can probably find something similar for python, there are a few lightweight sql generation libs. > The funniest SQL injection attacks I've seen have been against our search pages. Attackers assume that search is done as dynamic MySQL queries, that fact that you are actually using Sphinx or Solr seems to escape most wanna be hackers. bots. they see a form, they submit crap to it.
jcampbell1 13y ago
Prepared statements don't handle all cases, e.g. "ORDER BY :?" doesn't work.
- yogo 13y ago
  I think you can add your own kind of preparation/binding on top of the db engine's binding by introducing a little syntax for those cases. Basically what I'm saying is to properly sanitize what gets inserted then perform a string replace on the query string. It comes in handy for other clauses that normal binding does not cover. An example would be you would have your binding take care of something like :foo: as a sort of preprocessing before the db takes care of the others like :foo
- chiph 13y ago
  Another example (SQL Server specific, perhaps) is parameterizing the COLLATE option. You just can't do it, so you have to string-concat it, which means that your query cache has n versions of each query, where n is the number of locales your app supports. Good thing RAM is cheap.
dustingetz 13y ago
anti-ORM is not exactly a new sentiment - ORMs have been a hot & unresolved debate for two decades - but you need to be very careful about what you read on the internet, a lot of people express reasonably written opinions with an authoritative tone (like me) but are just wrong (hopefully not me haha). Find someone smart that you trust, like Rich Hickey or Greg Young, and try to understand what they say.
twistedpair 13y ago
Odd that few articles champion a limited database user. In my apps there is a crippled read only user, an update user with limited UPDATE/INSERT privileges, and finally an admin user that is in no way accessible from the app. There is no reason for your standard app user to have access to the core database object tables to do DDL lookups or mess around with the internals. Further, most queries should only run through the RO user. Finally, no user for your app should have DROP or SUPER privileges. You also should disable multi-query requests out the gate. Of course the dev's will need to remember to use the proper set of creds from their favorite SQL IDE, and there will be a few hiccups for 10min until you've got the proper creds set for the app user, but then you can limit the worries from an attack.

jroseattle 13y ago

Nice article, but this is training wheels. Maybe it's inferred, but this article should be pointed at the rookie developer.

magoon 13y ago
Which article is for intermediate and advanced?
- dmix 13y ago
  http://www.amazon.com/SQL-Injection-Attacks-Defense-ebook/dp/B008PQESLI/ http://www.amazon.com/SQL-Injection-Attacks-Defense-ebook/dp...
glhaynes 13y ago
I learned long ago to sanitize my inputs/etc so I don't think I'll write more secure code for having read it, but I hadn't ever thought through exactly how an attacker might do things like time-based blind SQL injection. So I found it interesting.
- jeltz 13y ago
  You should whenever you can always parametrized queries over sanitizing the input. Manually escaping or sanitizing should be the last resort.
  - PavlovsCat 13y ago
    Manually escaping or sanitizing should be the last resort. Why, other than "because one might forget it"? That is, what if bits of the query depend on various conditions and need to be constructed part by part? Keeping the placeholders and the actual values synced seems like a nightmare in those cases.. unless I am missing something.
    - ygra 13y ago
      One could imagine something like public static void AddParameterValue(this SqlCommand cmd, string paramName, object value) { if (cmd.CommandText.Contains("@" + paramName)) cmd.Parameters.AddWithValue(paramName, value); } to help set parameters only if they are referenced in the SQL statement.

sehrope 13y ago

Both raw SQL and using an ORM have their place. The latter is definitely less prone to unintended SQL injections but it's still possible. The reverse is true too. Raw SQL can be quite secure if you're not an idiot about it (golden rule: never build command strings from user input). If you use prepared statements across the board you don't even have to bother with sanitizing user input[1]. The main reason to use an ORM is for programmer efficiency. Sure I can write a bunch of CRUD operations but why bother if I can have the ORM do it for me? Since CRUD operations are either inserting a new row or accessing/updating by a primary key it'll be indexed as well so no perf issues[2]. The additional work by your app code for the ORM library is meaningless compared to the DB round trip anyway. My favorite part? Adding a new field goes straight to the model and nowhere else[3]. The big place for raw SQL is as the secret sauce on the meat and potatoes of your apps. Once you've got a real data set and you want to combine, slice, dice, you're not going to do that through an ORM. You want those queries to be as performant as possible. If you've structured your tables properly (proper foreign keys, normalization, etc) then the writing custom SQL will also be much more straightforward then trying to kludge together ORM commands to do what you want. On top of that, the work will happen on the database where it can filter it prior to your app processing it. For our app[4] most of the CRUD pages are handled by an ORM but there's quite a bit of custom SQL too. One example is for security authorizations. Validating security authorizations (can user X access DB y) is a hierarchical check. The logic is all done in a Postgres stored proc (well technically a function). Doing it via an ORM would inefficient, both in programmer time and computer runtime. [1]: You probably should though. It's generally a good idea to have some kind of white listing for what is an acceptable value for a field. Either way though you need to make sure you escape them when outputing HTML for webapps to prevent XSS. That combined with prepared statements when interacting with user inputs is the only right way to do things. [2]: For basic CRUD operations and even simple one-to-many lists. Beyond that things can and do get hairy but those aren't the majority of cases. The majority of app code is vanilla id-based select, inserts, updates, and deletes. [3]: http://en.wikipedia.org/wiki/Don%27t_repeat_yourself http://en.wikipedia.org/wiki/Don%27t_repeat_yourself [4]: http://www.jackdb.com/ http://www.jackdb.com/

dustingetz 13y ago
i think your third paragraph is more or less false for apps over a certain size, think enterprise-y stuff. it is certainly false for the class of apps that i work on - big pharma regulatory compliance software, very complex data model, hundreds of tables etc. Our ORM can express higher level queries and a wider set of queries than raw SQL - you can express a query in one line (one thought) that compiles down to quite a few nested SQL expressions. Finally, the problems of orm stem from the fundamental nature of SQL, so dropping into raw sql couldn't possibly fix them. You need something like Datomic or CQRS/ES to remove the object/relational impedance mismatch at a fundamental level. (This is analogous to why Git rocks compared to SVN; Git solves the problem of centralized mutable state at a fundamental level which opens the door for a better model of the problem and more powerful abstractions.)

AaronBBrown 13y ago

And of course, the highly relevant classic XKCD: http://xkcd.com/327/ http://xkcd.com/327/

jackmaney 13y ago

';DROP TABLE articles;--

twistedpair 13y ago
There was a chance it might work.

300bps 13y ago

Anything that teaches developers about security is a good thing including his article. One minor nit - ASP.NET has been mostly shielded from these types of things for 6 or 7 years. He used ASP.NET in his examples which meant he purposely turned off several protections to get the examples to work.