Hacker News Reader

Comment by john61 | Hacker News Reader

>> Fully prevent SQL injection by only using SQL prepared statements and stored procedures

> That is an extraordinarily inefficient and costly way to develop.

Why is this inefficient? Can you elaborate?

As mentioned beneath, the comment was secretly fixated on the 'stored procedures' side and not the 'prepared statements' side. Stored procedures have many costs associated to them; for example, they don't get version-controlled by default and they don't lend themselves easily to a development/production schism; both of these need to be solved by writing out some sort of database-synchronizer-script which will handle fallover. In addition usually you cannot issue breaking changes to an SPROC without scheduled downtime, so you have to intentionally have a flow of "create new SPROC, make code use the new SPROC, deploy across the codebase and audit for the old SPROC's use, finally remember to drop the old SPROC so that old code doesn't get recycled by someone who is looking for the first thing that does what they want." Compare that to just "commit a new patch, then upgrade servers as soon as possible".

Until you deal with important data, like records indicating money in a bank account. Then it's more like "tough shit you can't just slap an ORM on the direct table data".

Most people don't deal with important records though, and generally not people here (though a shout out to people from Stripe and other payments companies!). So I would t recommend stored procedures for this cloud either.

I do recommend prepared statements, but they do have a cost since it is two network calls for one query instead of one. Totally worth it too.

In my development style every database ddl change is done with a version controlled sql file piped trough psql and/or apgdiff. So I guess these points do not apply to me since there is no difference in normal code and database code handling.

I am not the original poster, but some queries can't be moved to stored procedures (depends on SQL software and library abilities). For example:

IN clauses can get tricky if prepared statements don't support arrays / lists of columns.

Adhoc queries get tricky where user could specify a parameter or not.

This answer is more about stored procedures than prepared statements.

That is right. The key point is to reduce exposure to SQL injection by not formatting queries. Prepared statements help solve a whole class of bugs at a lower level.

Reply on news.ycombinator.com