Prepared Stmt vs. Queries consensus

Square1

Hi all:

I have read on this board (by a respected member) not to use prepared statements for everything. More code/2ble trip to the server were the reasons.

But I have also read that sanitizing input is not 100% as effective as binding and some people say to use them all the time.

That being the case, can I get a consensus...

If you were starting a site from scratch would you use all prepared statements or just where beneficial? Would you use all queries????

Right now I am using all prepared statements and want to make sure I am not going astray.

As many opinions as possible appreciated!

Thanks!

bradgrafelman

Square1 wrote:
But I have also read that sanitizing input is not 100% as effective

Any chance you can cite your source on that? As I said in your previous thread (at least, I think it was yours), either data is sanitized or it isn't. It doesn't matter what method you use - prepared statements or manually sanitizing the data, e.g. via [man]mysqli_real_escape_string/man - if it's sanitized, great, if not, then you've got security problems.

The problem is that most people think making a website in PHP/SQL is as simple as reading a couple of tutorials (or, even worse, just looking at some existing PHP code they found somewhere and assuming they've "learned" PHP once they know how it works). If that were the case, then my university sure is shortchanging people by the several classes they offer that just scratch the surface of web design, security, SQL, etc. Sanitizing data isn't as simple as adding slashes before a quote (hence why [man]addslashes/man is inappropriate/inadequate for preparing data to go in a SQL query).

It is possible that this is why you have read that "sanitizing input is not 100% as effective as binding." But note that that should instead be worded something like: "an inexperienced programmer who improperly attempts to sanitize input is not 100% effective as binding." In that case, I would whole-heartedly agree. However, I don't think the solution is to dumb down the world of programming and just tell the programmer: Well, since you don't know how to properly sanitize data, just use prepared statements - that way you can remain ignorant about how security is being provided and all will be well!

Heck, if you agree with that last statement, you are most likely a Microsoft employee.

Square1 wrote:
If you were starting a site from scratch would you use all prepared statements or just where beneficial?

For a query that is executed only once, I've personally never wasted the extra codespace and execution time of using a prepared statement. I just can't see the benefit of doing so.

However, if you plan on re-using a query, then sure, turning it into a prepared statement would probably be wise.

Square1 wrote:
Right now I am using all prepared statements and want to make sure I am not going astray.

And there's not really any fundamental problems with doing that; I doubt there would be any appreciable effect on performance if you switched to using regular queries. If you feel more comfortable using prepared statements, then do so.

laserlight

Square1 wrote:
But I have also read that sanitizing input is not 100% as effective as binding

If we are talking about effectiveness against SQL injection, then both approaches are equally effective. However, prepared statements, unless merely simulated, provide a better margin of security because the data is separated from the SQL statement: it is impossible to tamper with the SQL statement by tampering with the data, whereas it is still possible that a bug in the relevant escape function could allow such tampering to happen.

Furthermore, associating a value with a placeholder and sanitizing input are combined in the same step of binding with prepared statements, whereas they are separate steps when an escape function is used, thus making it possible that the escape function is not applied or applied incorrectly.

Square1 wrote:
If you were starting a site from scratch would you use all prepared statements or just where beneficial?

I would use prepared statements where feasible. A case where it may not be feasible would be with an IN clause: you cannot bind a list, so you may need to use an escape function on the elements of the list instead.

Square1

Thank you guys.

If there isn't any outside variable being input I am using queries, but for POST, GET, COOKIE, SESSION etc it seems like the benefit of not dealing with escapes outweighs the negatives.