Is It Overkill To Use Prepared Statements for Placeholder Binding Alone?_问答_开发者

A know a lot of people that use prepared statements, for the placeholder binding alone. That is, they don't intend on issuing the same statement more than once, wit开发者_运维百科h different values. They simply feel using PS in this manner is more secure.

My understanding of MySQL's PS, is the SQL is sent as a single transmission, followed by one or more transmissions to send the values. So using PS for a single query is less efficient that using a plain query, which is done in a single transmission.

Do the security benefits PS offer out weigh the loss in efficiency? I don't think so, because I don't think it's that hard to properly escape values before adding them to your SQL.

What are your thoughts?

A prepared statement is compiled and stored on the DBMS. These statements may be cached and reused.

Imagine the benefits when hitting the same page multiple times.

Further, some database engines (Oracle for example) may impose a hard statement limit and trust me (I know from experience), you do not want to exhaust this.

Do the security benefits PS offer out weigh the loss in efficiency?

I think that yes, they probably do. Even if using prepared statements was really very inefficient, though, wouldn't it be prudent to gather some figures demonstrating that that inefficiency adds up to some kind of notable impediment to your operations? If it's less efficient but your web server can still handle it just fine, does it matter too much? You have to quantify what the difference is before you can say how much it would matter.

As it is, you're weighing security against a fairly nebulous idea of what might be efficient. That's probably going about it the wrong way.

Edit: In response to first comment:

Of course the argument can be turned the other way, and you can argue that some kind of evaluation should be made of how much more secure prepared statements are than query-building, if at all. But this is less amenable to quantification than your concerns about script efficiency.

I would propose that everyone's goal in this is to arrive at a solution that they can be reasonably confident protects them from exploits; and certainly if you use prepared statements religiously, or you conscientiously escape query variables whenever needed, then you have done that. What you should really be thinking about, IMO, is: how systematic is my approach to avoiding SQL exploits? How much scope does it offer for mistakes to happen, for failing to spot when you are creating code that will have a problem?

If every time you do a database query you have to conscientiously remember to go through every variable that will be used and escape it for that purpose, that leads to lengthy code and a great possibility that you will overlook some variable or another. Cutting and gluing strings is also somewhat untidy by its nature. If you have some level of abstraction that requires you to identify each query variable and its type, and that causes the query to fail if you don't do so, then whether that is prompted by the use of prepared statements or not, you are already doing things in a more systematic way and this helps you to avoid oversights that would bypass your careful protection everywhere else.

I don't think it should be a matter of efficiency. I doubt that you'll measure a meaningful difference, especially when compared to the network latency for database calls and the inefficiencies introduced by the rest of your code.

The benefit of guarding against SQL injection alone is worth the price of admission all by itself.

I think this is an example of a micro-optimization that Donald Knuth warned against.

But don't speculate or rely on opinions you get here. Be a scientist and get some data. If the data supports your case, by all means go for it.