Hacker News Reader

Comment by FooBarWidget | Hacker News Reader

> That is an extraordinarily inefficient and costly way to develop. The correct way to protect against SQL injection is to use a framework/driver which guarantees escaping and to be cautious about what you allow into queries.

I agree with you, but I don't think that's what the author meant. Nowadays, when most people talk about "prepared statements" and "stored procedures", they are just conflating those things with escaping. I think they're trying to say, use something with an API that prevents injection bugs, rather than actually use database prepared statements.

I find that 9 out of 10 times, when people talk about "prepared statements" they are referring to something like the PHP bind_param thing (https://www.w3schools.com/php/php_mysql_prepared_statements....), rather than this sort of stuff: https://www.postgresql.org/docs/9.3/static/sql-prepare.html

View on HN

History has many examples of "safe escaping" that didn't turn out to be so safe after all.

I'd rather use proper prepared statements than rely on string escaping.

I looked at the two linked pieces of documentation, and to my naive eyes they look like they perform the same task:

PHP: "Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query", meaning that the statement is prepared server-side and parameters are sent separately.

Postgres: "A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.... Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again."

Is there a difference between the two, and could you elaborate?

ActiveRecord uses actual, PostgreSQL prepared statements in many cases. Eg if you execute `User.where(email: "foo@bar.com")`, it will prepare, bind and execute the query separately, with "foo@bar.com" being sent to PG as a bound parameter. You can see this if you flip the proper flags in the PG config and tail its log.

In other cases, like `User.where("email like ?", "#{params[:email_like])}%")`, AR will escape the provided value itself and no bind parameter will be used. However, it will still use a prepared statement, which at least means that only one database query will execute; no matter what AR does or fails to do with it's escaping, you won't execute both the `SELECT` and a `DROP TABLE` because a prepared statement can only be one statement.

Reply on news.ycombinator.com