Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

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.



> I get the concept of the ORM, but I don't really plan to switch database anytime soon

not sure if you get the concept of ORM, because switching databases is not what ORMs are primarily designed for, it's more of a common feature / side effect that may get invalidated when you use unsupported dbms features.

> and I have needed for the flexibility of having "no ORM".

http://docs.sqlalchemy.org/en/latest/core/index.html

http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html

or a clojure example http://sqlkorma.com/ you can probably find something similar for python, there are a few lightweight sql generation libs.

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

bots. they see a form, they submit crap to it.


anti-ORM is not exactly a new sentiment - ORMs have been a hot & unresolved debate for two decades - but you need to be very careful about what you read on the internet, a lot of people express reasonably written opinions with an authoritative tone (like me) but are just wrong (hopefully not me haha). Find someone smart that you trust, like Rich Hickey or Greg Young, and try to understand what they say.


Odd that few articles champion a limited database user. In my apps there is a crippled read only user, an update user with limited UPDATE/INSERT privileges, and finally an admin user that is in no way accessible from the app.

There is no reason for your standard app user to have access to the core database object tables to do DDL lookups or mess around with the internals. Further, most queries should only run through the RO user. Finally, no user for your app should have DROP or SUPER privileges. You also should disable multi-query requests out the gate.

Of course the dev's will need to remember to use the proper set of creds from their favorite SQL IDE, and there will be a few hiccups for 10min until you've got the proper creds set for the app user, but then you can limit the worries from an attack.


I take the exact opposite view. I use an ORM where you think in SQL (SQLAlchemy) and can build sql queries using functions named the same as the SQL.

If you use an ORM then SQLi should be impossible, unless there's a bug in your ORM.


as you point out you can build the sql query. I've not seen any ORM that validates the SQL you pass it to build the query. This is how SQLi happens in the ORM tier.

objects = orm.rawQueryForObjects("select * from people where name = '" + name + "')


You build the sql query using chained functions, so it's safe:

objects = People.query.filter_by(name=name).all()

When I said 'build the sql query' I meant using SQLAlchemy core, which is also safe:

objects = select([People]).where(People.name == name)


Even sqlalchemy has the mechanism I pointed out. I've never seen an ORM not have it because it becomes important if you're putting an ORM on top of a previously designed database or optimising queries.

session.query(Object).from_statement("SELECT * ...")

http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalch...


Sure, and shooting yourself in the foot is also possible by pointing the gun down at the floor ... doesn't mean it is a good idea.

Generally the same statement can be built using internal SQLAlchemy...

But if you want to do your own, you can in sqlalchemy while still being as safe:

http://docs.sqlalchemy.org/en/rel_0_5/sqlexpression.html#usi...


> "Sure, and shooting yourself in the foot is also possible by pointing the gun down at the floor ... doesn't mean it is a good idea."

But it's possible and people do it. Which is, I believe the point was, the counterpoint to "SQLi should be impossible".

Sure, One can avoid shooting themselves in the foot with an ORM. But that's also true in SQL.


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


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


Another example (SQL Server specific, perhaps) is parameterizing the COLLATE option. You just can't do it, so you have to string-concat it, which means that your query cache has n versions of each query, where n is the number of locales your app supports. Good thing RAM is cheap.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: