4 ms·

I have projects that use ORMs and projects that do not use ORMs. I have specific reasons for both types, and in certain circumstances one or another is more app

I have projects that use ORMs and projects that do not use ORMs. I have specific reasons for both types, and in certain circumstances one or another is more appropriate. ORMs are a dependency that are often constantly updated, prone to corner cases, and require a lot of library specific knowledge-- but they often make code more secure against small mistakes or tossing in a string interpolation, which is important when working with others, especially junior developers. They can be quite nice to work with in having type checked queries, building up queries from pieces using an API, but they also tend to be heavy and miss some features of your DB. Raw SQL on the other hand, is very flexible, standard, easy to read, and anyone who knows SQL can jump in. You can do REPL for queries and copy in, and edits to SQL are easy. The only dependency you need is the standard database drivers, and updates don't typically break things. If you have a basic understanding and use it right, security is great as well.

px43 6y ago
> If you have a basic understanding and use it right, security is great as well. I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs. Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections. It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.
- sjaak 6y ago
  By Sturgeon's law [1] 90% of all code is crap, so it doesn't surprise me that that 90% of code contains sql injections ;-) [1] https://en.wikipedia.org/wiki/Sturgeon%27s_law https://en.wikipedia.org/wiki/Sturgeon%27s_law -> "Ninety percent of everything is crap."
- aww_dang 6y ago
  In the Java ecosystem there is the PreparedStatement. ORM isn't required to avoid SQL injections. The two concepts maybe conflated with some frameworks, but they aren't necessarily related.
- orolle 6y ago
  I disagree. As long as you use prepared statments and bounded parameters, your application is safe from SQL injections. NEVER use string concatiation to generate any SQL queries - not in your app and not in your database! Its unsafe and slow. https://security.stackexchange.com/questions/15214/are-prepared-statements-100-safe-against-sql-injection https://security.stackexchange.com/questions/15214/are-prepa...
  - px43 6y ago
    That's easy enough to say, but time and time again I see codebases, even ones making extensive use of prepared statements, falling back to doing string concatenation from time to time. Prepared statements etc are an example of "opt-in security", which is a good band-aid to have for quickly fixing up old code, but it still allows for some pretty egregious errors. Again, with the seat-belt analogy. As long as you're safe and careful all the time, seat-belts are worthless. Therefore seat-belts are only for dumb, reckless people.
    - the_af 6y ago
      Then again, prepared statements (and SQL injection) are a solved problem. Imagine what people who can't bother to use prepared statements would do with an ORM in non-trivial cases.
- fabian2k 6y ago
  Anyone writing raw SQL should use parametrized queries for all cases where this is possible. That has been the recommendation for a long time now, and there is really no excuse to not doing that. There are some cases where you can't use them, e.g. dynamic queries where you modify which columns are queried or larger parts of the entire query. But that more of an exception, and you do need to be careful about only using whitelisted terms when you modify the raw SQL part that can't be replaced by parameters.
- Person5478 6y ago
  > I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. Just parameterize the query and you're done. If someone isn't doing so in 2020 they're either working on a very old system or they're not doing it right. SQL injection is just not a reason to avoid raw SQL. One of the reasons I love Dapper.net is because it allows me to use raw SQL and helps solve the only pain point I have with raw SQL, dynamically building queries.

px43 6y ago

> If you have a basic understanding and use it right, security is great as well. I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. I'm sure there are a ton of reasonable use cases for raw SQL, but when I see the same dumb mistakes over and over again, it just makes me shake my head and wish more people just used ORMs. Major breaches happen every day due to SQL injections, so building up a narrative that developers can do raw SQL right if they're smart enough is just irresponsible IMO. Over and over again when I tell developers that they should use an ORM, they get offended, and then I end up finding trivial SQL injections. It kind of feels like telling people that it's perfectly safe to drive without a seat-belt so long as you're a good driver.

sjaak 6y ago
By Sturgeon's law [1] 90% of all code is crap, so it doesn't surprise me that that 90% of code contains sql injections ;-) [1] https://en.wikipedia.org/wiki/Sturgeon%27s_law https://en.wikipedia.org/wiki/Sturgeon%27s_law -> "Ninety percent of everything is crap."
aww_dang 6y ago
In the Java ecosystem there is the PreparedStatement. ORM isn't required to avoid SQL injections. The two concepts maybe conflated with some frameworks, but they aren't necessarily related.
orolle 6y ago
I disagree. As long as you use prepared statments and bounded parameters, your application is safe from SQL injections. NEVER use string concatiation to generate any SQL queries - not in your app and not in your database! Its unsafe and slow. https://security.stackexchange.com/questions/15214/are-prepared-statements-100-safe-against-sql-injection https://security.stackexchange.com/questions/15214/are-prepa...
- px43 6y ago
  That's easy enough to say, but time and time again I see codebases, even ones making extensive use of prepared statements, falling back to doing string concatenation from time to time. Prepared statements etc are an example of "opt-in security", which is a good band-aid to have for quickly fixing up old code, but it still allows for some pretty egregious errors. Again, with the seat-belt analogy. As long as you're safe and careful all the time, seat-belts are worthless. Therefore seat-belts are only for dumb, reckless people.
  - the_af 6y ago
    Then again, prepared statements (and SQL injection) are a solved problem. Imagine what people who can't bother to use prepared statements would do with an ORM in non-trivial cases.
fabian2k 6y ago
Anyone writing raw SQL should use parametrized queries for all cases where this is possible. That has been the recommendation for a long time now, and there is really no excuse to not doing that. There are some cases where you can't use them, e.g. dynamic queries where you modify which columns are queried or larger parts of the entire query. But that more of an exception, and you do need to be careful about only using whitelisted terms when you modify the raw SQL part that can't be replaced by parameters.
Person5478 6y ago
> I'd say about 90% or so of the code I look at where someone is building raw SQL queries has trivial SQL injections in it. Just parameterize the query and you're done. If someone isn't doing so in 2020 they're either working on a very old system or they're not doing it right. SQL injection is just not a reason to avoid raw SQL. One of the reasons I love Dapper.net is because it allows me to use raw SQL and helps solve the only pain point I have with raw SQL, dynamically building queries.