Necessity of preparer suite in PHP

[Cupidvogel]

Hi, we need prepare statements in MySQL to dynamically query or update databases, for it has no exec or eval type of statement that will execute the statement as if it were code, right? However, why do we need a whole slew of prepare related functions in PHP-MySQLi as well? Surely all the variables we intend to bind, we can easily include them in a normal query string where they will auto expand, and then we pass the statement to MySQLi, from where we will get the same result?

[requinix]

If you like rolling your own SQL injection prevention and type conversions then sure.

If you won't be executing one query repeatedly using different values, or don't care about inefficiency in doing so, then sure.

If you don't care that the mysql extension is being deprecated then sure.

 

If you don't want any of the advantages of prepared statements then sure.

[Cupidvogel]

So if I use a particular query, like Select * from foo where boo='bar', and I need to use it more than once in the same script, using prepared statements will increase the speed/efficiency?

[kicken]

So if I use a particular query, like Select * from foo where boo='bar', and I need to use it more than once in the same script, using prepared statements will increase the speed/efficiency?

 

Yes.  It's more common/useful for things like INSERT or UPDATE queries but preparing the query once and executing it several times will result in better efficiency.

 

When you run a query there are two steps to it.  First, the server parses and analyses the query to determine the best method of execution (ie, which indexes to use, where to grab data from, what order to get it, etc). After that it executes the devised plan and returns the results.

 

When your running the queries each time, such as with the old mysql_query function then both these steps have to be processed on every run of the query.

 

When you use a prepared query the steps are broken up.  When you first prepare the query the server will parse and analyse it and devises a plan.  Every execution after that will used the devised plan, saving time that would have been spent re-parsing and re-analyzing the query under the non-prepared methods

 

[Cupidvogel]

Wow! So now tell me, is there any situation at all where I should be using $mysql->query($query) instead of the prepared statement? That is, for any data coming from the client side, whether data they have inputted, or data the page itself had hardcoded (for example, attribute of some element), should I always use prepared statement?

[kicken]

If your only going to run a query once (which is common for SELECT queries) and the query does not depend on any user inputted values (such as querying app config data or similar) then you can just run the query and skip all the prepare steps.

 

If your query does use user inputted values, but you've sanitized them by some other method, then you could also use query rather than prepare.  I do this for queries where the only values used in it are a list of ID numbers inside an IN clause, since there is no way to bind a list of values, only single values.  I run intval() across the array to sanitize the values, then just concatenate them into the query and run it.

 

All other cases I use a prepared query for though.  The sql injection protection is reason enough, any other benefit is just icing on the cake.

 

[Cupidvogel]

Thanks.