3 ms·

Don't ORMs use parameterized queries by default?

tptacek 16y ago
Yes, but they don't parameterize the sort order on every sortable table, or the limits used in pagination, or the custom join expressions ORM developers inevitably write.
- RyanMcGreal 16y ago
  Thanks for clarifying. Every time I think I have a handle on website security, it turns out to be more complex and insidious than I thought.
- rbanffy 16y ago
  I am quite sure Django's ORM (the one I use every day) would properly protect limits and pagination.
  - tptacek 16y ago
    That's nice. Until you forget to quote_name a dynamically selected table name.
    - rbanffy 16y ago
      Why would you want to name a table dynamically?
      - tptacek 16y ago
        Thanks for helping make my point about real-world dev vs. on-paper dev. But, to be clear, you don't often create tables with dynamic names (though you will sometimes; for instance, to repeatedly load huge datasets, which comes up in time-series apps)... but you very often need to select which table to use at runtime based on user inputs. Again, in real-world apps. For what it's worth, I believe the same quote_name() mistake pops up with column names too. But you probably don't dynamically select those based on inputs either.
        deleted 16y ago
        [deleted]
        rbanffy 16y ago
        I most certainly won't create tables named after web input. ORMs are there to shield you from database details. I don't care whether the underlying database is a MySQL instance running on Amazon's cloud, an Oracle RAC down our datacenter or even a non-relational animal we use for very high volume operations. I don't care if the data is in tables or a tree. The kind of stunt you are trying to convince us is reasonable should never be done on top of the ORM, but under it, or, better, by its side. If you have a humongous dataset to load, don't do it through the ORM - do it directly on the datastore. On a side note, I find your tendency to rely on name-calling and insinuations most disturbing and quite inappropriate for a place like HN.
        tptacek 16y ago
        All I can tell you is what I see other people's apps doing, routinely, with a variety of different ORMs. You claimed ORMs are a better answer to SQL Injection than stored procedures. I'm sorry it upsets you to hear this, but that's simply incorrect. The apps that I've seen do best against SQLI had an enforced policy that front-end devs couldn't write queries, that their access to the database was restricted through stored procedures, and that access was further restricted with database permissions. Stored procedures aren't a panacea either, but they're definitely more reliable than the Django ORM.
        rbanffy 16y ago
        By themselves, stored procedures are not going to prevent SQLI. You will also have to lock down the database. You should also use parametrized queries to call those procedures, or else you would be able to inject something to get some information you shouldn't have access to or, at least, do something that can bring down the server (like looping around the stored procedure in a DoS attack). My problem with stored procedures is that when you go that way, your business logic will end-up inside the database or, worse, scattered through layers of application and database (ever tried to do version control on stored procedures?), something really bad when you need to know what is going on with the server or when you need to move to a different database. If you check your ORM thoroughly (I never did that as thoroughly as I wish I had with Django) and you never, ever, write SQL in your business logic (something you really should go for) you will be in better shape than if you write SQL code that calls stored procedures. It's easier for the DBA to slip up in a your-business-specific way than for the ORM maintainer. Also, with the DBA, only a few people will have access to catch the mistakes whereas lots of people read (and abuse) ORM code regularly. I think the most important point you mentioned is not the stored procedure, but the lock down. If the app isn't allowed to mangle the data it should not mangle, you are mostly good. The best part is that locking down the database can happen without touching the ORM and is more or less portable across different databases.

Yes, but they don't parameterize the sort order on every sortable table, or the limits used in pagination, or the custom join expressions ORM developers inevitably write.

RyanMcGreal 16y ago
Thanks for clarifying. Every time I think I have a handle on website security, it turns out to be more complex and insidious than I thought.
rbanffy 16y ago
I am quite sure Django's ORM (the one I use every day) would properly protect limits and pagination.
- tptacek 16y ago
  That's nice. Until you forget to quote_name a dynamically selected table name.
  - rbanffy 16y ago
    Why would you want to name a table dynamically?
    - tptacek 16y ago
      Thanks for helping make my point about real-world dev vs. on-paper dev. But, to be clear, you don't often create tables with dynamic names (though you will sometimes; for instance, to repeatedly load huge datasets, which comes up in time-series apps)... but you very often need to select which table to use at runtime based on user inputs. Again, in real-world apps. For what it's worth, I believe the same quote_name() mistake pops up with column names too. But you probably don't dynamically select those based on inputs either.
      - deleted 16y ago
        [deleted]
      - rbanffy 16y ago
        I most certainly won't create tables named after web input. ORMs are there to shield you from database details. I don't care whether the underlying database is a MySQL instance running on Amazon's cloud, an Oracle RAC down our datacenter or even a non-relational animal we use for very high volume operations. I don't care if the data is in tables or a tree. The kind of stunt you are trying to convince us is reasonable should never be done on top of the ORM, but under it, or, better, by its side. If you have a humongous dataset to load, don't do it through the ORM - do it directly on the datastore. On a side note, I find your tendency to rely on name-calling and insinuations most disturbing and quite inappropriate for a place like HN.
        tptacek 16y ago
        All I can tell you is what I see other people's apps doing, routinely, with a variety of different ORMs. You claimed ORMs are a better answer to SQL Injection than stored procedures. I'm sorry it upsets you to hear this, but that's simply incorrect. The apps that I've seen do best against SQLI had an enforced policy that front-end devs couldn't write queries, that their access to the database was restricted through stored procedures, and that access was further restricted with database permissions. Stored procedures aren't a panacea either, but they're definitely more reliable than the Django ORM.
        rbanffy 16y ago
        By themselves, stored procedures are not going to prevent SQLI. You will also have to lock down the database. You should also use parametrized queries to call those procedures, or else you would be able to inject something to get some information you shouldn't have access to or, at least, do something that can bring down the server (like looping around the stored procedure in a DoS attack). My problem with stored procedures is that when you go that way, your business logic will end-up inside the database or, worse, scattered through layers of application and database (ever tried to do version control on stored procedures?), something really bad when you need to know what is going on with the server or when you need to move to a different database. If you check your ORM thoroughly (I never did that as thoroughly as I wish I had with Django) and you never, ever, write SQL in your business logic (something you really should go for) you will be in better shape than if you write SQL code that calls stored procedures. It's easier for the DBA to slip up in a your-business-specific way than for the ORM maintainer. Also, with the DBA, only a few people will have access to catch the mistakes whereas lots of people read (and abuse) ORM code regularly. I think the most important point you mentioned is not the stored procedure, but the lock down. If the app isn't allowed to mangle the data it should not mangle, you are mostly good. The best part is that locking down the database can happen without touching the ORM and is more or less portable across different databases.