4 ms·

It's amazingly easy to prevent for high value target type web apps use stored procedures only. The web app db user should only have permissions to execute appro

• koolba 10y ago
  Stored procedures aren't a panacea for SQL injection. The real answer is to use parameters for any user input. Otherwise if you're concatenating strings to invoke your stored proecedures you're in the same boat.
  • kodablah 10y ago
    The sad part is many libs force you to accept prepared statements with your parameters or you get no parameters at all.
    • ThisIs_MyName 10y ago
      Care to elaborate?
  • qaq 10y ago
    It's extremely easy to validate in automatic way that only proper call's are used to call the sprocs also if you limit web app db user to only have permission to execute appropriate stored procedures you mitigate a good number of attacks even if someone did call the sproc in unsafe manner that allows sql injection now the attacker needs to guess sproc names and signatures and still limited in what he/she can do. This assumes that sprocs are themselves not written to concat sql and then execute it :)
  • justinclift 10y ago
    As a side note, it's not always possible to use parameter binding for all parts of a SQL query. :( For example, when querying user uploaded databases (not a typical use case), the SQL query needs to include the table name. At least with SQLite (unsure about PostgreSQL), table names can't be bound in a parameter. They need to be part of the non parameterised query string, thus string smashing. Field/column names are probably similar too, though I haven't checked (yet).
    • koolba 10y ago
      If you're doing anything that esoteric hopefully you understand proper identifier escaping. If not, there's bound to be much worse throughout your app.
    • snuxoll 10y ago
      That's what libraries like sequel, SQLAlchemy or Jooq are for. I'm looking at implementing some crazy dynamic queries in a CRM system I'm prototyping right now and SQLAlchemy is my saving grace to avoid shooting myself in the foot.
    • true_religion 10y ago
      That's true, but an ORM like ActiveRecord ought to protect you from this because you are required to specify valid column names and table names before using it. It should be able to check if an invalid column or table name is used.
• DaiPlusPlus 10y ago
  I see Dynamic SQL being used the most for custom queries that require dynamic columns, or especially, dynamic ordering. Using sprocs won't help you there as you still need Dynamic SQL under the hood. I think the best solution is something like Linq-to-Entities with manually defined order-by logic for each query (e.g. an enum defining the sort-order possibilities) or reflection-based matching, neither of which is pretty.
  • smnc 10y ago
    Dynamic SQL is not the problem, you can safely construct queries on runtime in most RDBMS-s, the key thing is to let the DB treat user input as parameters. You can concat strings to create dynamic queries all you want, just don't blindly shove user input in there, construct the query dynamicaly with parameter placeholders and then bind the user-passed input as parameters. Of course, there's still the matter of those queries not nessarily being syntactically or semantically correct which will only bite you on runtime, but that's a different story. If you're doing dynamic SQL you either know what you're doing, or don't know that you don't know what you're doing. Although that probably goes for most of things in life.