
Everything you wanted to know about SQL injection (but were afraid to ask) - ssclafani
http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html#
======
sehrope
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/)

~~~
ecopoesis
I'm not convinced ORMs ever have a place. Using SQL do do CRUD-type things
just isn't that hard, and using the raw SQL is generally much clearer.

I think the Anorm developers say this best: "SQL is already the best DSL for
accessing relational databases. We don’t need to invent something new."
[http://www.playframework.com/documentation/2.1.1/ScalaAnorm](http://www.playframework.com/documentation/2.1.1/ScalaAnorm)

~~~
dustingetz
meh, i have used anorm. their site jabs at ORMs and jabs at Slick (typesafe
SQL dsl in scala); but the wording of both jabs make it clear that they've
missed the point of both tools. Slick is not about types - its about adding
one-off functions to remove all the repition from complex SQL - and ORM isn't
about hiding SQL under the hood, it's about expressing very complex queries at
a higher level than is possible in SQL. Just because someone wrote it on the
internet does not make it so.

If your app is simple CRUD on 10 tables, anorm is great. But even for CRUD on
complex types (nested objects, objects with collections of objects, objects
with values that aren't primitives e.g. dictionary types where possible values
aren't known until runtime...) those queries will be crazy complex and
repetitive in raw SQL and you will quickly see you need an orm (or move off
sql to some other acid alternative like datomic). Anorm would be an ideal
layer to write your own ORM in though.

~~~
asdasf
That seems totally backwards. If your app is a simple CRUD app, then ORMs save
you time. For complex apps, ORMs generally can't even express what you want,
or can only do so in incredibly inefficient ways, so you end up dropping down
to SQL anyways.

~~~
dustingetz
_" ORMs generally can't even express what you want, or can only do so in
incredibly inefficient ways, so you end up dropping down to SQL anyways"_

on the project/team i work on, this statement is false. If it's true for some
other team, maybe they are "doing it wrong". I'm sure it depends on one's
particular ORM implementation, and whether it is any good, and what it's
design goals are.

~~~
asdasf
What ORM are you using? I've never seen or heard of one that people didn't
have to drop down to sql with (or a pseudo SQL language that is converted
directly to SQL).

~~~
dustingetz
a custom one actually, and it does have tons of warts and i'd rather be using
datomic, but it does an exceptional job of expressing high level queries that
expand into a mess of nested SQL expressions.

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

~~~
jcampbell1
Prepared statements don't handle all cases, e.g. "ORDER BY :?" doesn't work.

~~~
yogo
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

------
AaronBBrown
And of course, the highly relevant classic XKCD:
[http://xkcd.com/327/](http://xkcd.com/327/)

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

~~~
bigiain
Troy writes largely for a less-technical-than-HN-commenters audience. You're
unlikely to see to many "Wow, there's something I didn't know" comments here
in response to his articles – but I'll bet there's many many heads nodding and
thinking "I wish I'd had this article to show a certain cow-orker before he
did $newbie_mistake[0]"

~~~
ams6110
Or, let's be honest: I wish I'd read this article before I learned this stuff
the hard way.

------
jackmaney
';DROP TABLE articles;--

~~~
twistedpair
There was a chance it might work.

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

