I have just got hold of a MySQLi class (a wrapper to the built in one), and for EVERY query sent they have used prepared statements.
Is this right? I expected it to just send using the mysqli_query function (with the prepared statements option if selected).
Should you send ALL queries using a prepared statement?
What (if any) are the downfalls of using prepared statements?
The downside for people trying to hack your database is that it is impossible for them to inject code when prepare statements are used. It keeps the code and data completely separate.
The only downside for you is that you can’t dynamically pass table names into the query in the 0.000000000001% of situations where it would be useful to be able to do that (I may have overestimated the percentage of situations where this would be useful but that doesn’t significantly change the answer).
Why would you expect a “turn off security” option for the class?
Its also worth noting that prepared statements are two trips to the db. So the number of trips to the database increases significantly. Not really an issue if MySQL is on the same server though. However, if its not located on the same server, it can become an efficiency issue.
Prepared statements by themselves really don’t help avoid injection. using them correctly does. There is nothing stopping anyone from embedding user input and running it as a prepared statement. In which case all the advantages of prepared statements are lost.
felgall makes me think I want to use prepared statements for every occasion.
oddz has made me think the opposite.
Do most decent programmers…
(a) Always use PS
(b) half and half
(c) hardly ever use PS
Is it the kind of think you should do all or nothing?
Personally, I prefer the flexibility of not using prepared statements. I’m probably one of the few, but that is what I prefer. If I must use prepared statements, than fine. However, I prefer not to (awaiting the rocks).
One of the other things is debugging SQL can a pain, using prepared statements, dependent on how many placeholders exist. You can’t just echo the SQL and run it. You need to print it, than make sure you manually replace all the placeholders. I can’t stand doing that, considering its fairly often that I need to print SQL and check it manually when developing.
None the less, you should experiment with both and draw your own conclusion.
Thanks for the help oddz.
Felgall, you seem to have hardly any reasons against prepared statements.
What do you think of the ‘two trips to the db’ issue?
I’m currently thinking of not bothering at all with PS.
I would actually advise that for production purposes you utilize them. If anything else they will enforce separation of of user input. So later down the line if you decide to not use prepared statements you will have a better understanding of the data that needs to be escaped when building a raw string.