Hacker News Reader

Comment by px43 | Hacker News Reader

> 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.

View on HN

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.

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-prepa...

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.

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.

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.

> 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.

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 -> "Ninety percent of everything is crap."

Reply on news.ycombinator.com