I was wondering if there was any difference between the MySQL PREPARE/EXECUTE clauses and the PHP mysqli prepare/execute methods? Are either better or worse at preventing injections?
I am curious because I am writing a few database stored procedures and in one, the table and attributes are not known on compilation. I could write the data in as static, but the query is a bit complex and it would just bloat the procedure with a lot of control logic. It got me thinking about this though and I just wondered whether it is better if, when I just needed a simple statement, I write a short procedure with dynamic SQL or just prepare and bind with PHP.
Also I apologize if this is a repeat and would of course welcome a link to an already answered SO question. However, I looked generally on google and could not really find much in the way of a specific answer to this.
Advertisement
Answer
PHP mysqli is a layer, in PHP, around MySQL’s prepare / execute functionality.
Both will keep you safe from SQL injection as long as everything that comes in from users is stuffed into a bound variable.
Prepared statements, handled either way, make for more efficient high-volume operation too. In MySQL the efficiency gain is modest compared to the high-priced DBMSs like Oracle, but it is still worth every bit of the trouble.
If you need to have table names as “variables” in your app, that’s OK, You can’t treat table names as bound variables though. So you need to be totally paranoid about any user input that results in the construction of these table names.