Prepared statement, the new thinking, worth it?

[leatherback]

Hi All,

I thought I'd open a little discussion on the benefits of prepared statements, the amount of time required to convert projects etcetc.

So.. Really. How much benefit do you get from prepared statements? Should I start converting all my old scripts into prepared statement based stuff before I do anything else? Or can I sit down, relax, and over the course of the next year or so convert thing? I have noticed that some scripts do not work anymore, so I need to convert thoses, and can include new style mysql queries in those. But should I be converting all of them!?

I do not build sites for a living. I have a day-job, and all I do on websites happens in my spare time. However, I have a few large projects which would be a crime to convert to the new setup. I just spent the last 2-3 months cleaning up an old content manager I build years ago, based in php 4.3. And now my provider has upgraded their servers to v5. (Yes, I asked them in December whether they would be upgrading soon and they didn't know..). So.. What is the benefit? Shall I toss the old scripts, and basically start afresh?

I'd like to hear some opinions..

J.

[MarkR]

I have used them almost* exclusively in all projects I've worked on for several years.

It really is the BEST way to get data into the database, there are no more funky escaping problems.

I recommend that you do the same on all new projects.

Remember that in some cases, prepared statements are emulated, but that is not a bad thing (in fact it can be better in some cases).

Mark

• There are some cases where you cannot pass something as a prepared statement parameter, e.g. WHERE something IN (1,2,3)

[leatherback]

MarkR wrote:

It really is the BEST way to get data into the database, there are no more funky escaping problems.

Yes, I read that somewhere late last night..

Do I really not have to worry about the string input anymore, and can I just use the $_POST[''] as input for prepared-statement based queries?

[Shrike]

You need to bind each parameter to a placeholder in the query, e.g.

$db->prepare( 'SELECT ... WHERE thing = ?', array( $_POST['id'] )); // ADODB

But yes, as Mark says the underlying software will take care of quoting issues. This is great when databases have different escaping strategies - i.e. MySQL and MSSQL.

[Roger_Ramjet]

The other main benefit of prepared statements is the greater processing efficiency: the query does not need to parsed every time you use it.

Of course most of the time a script should not be running the same query repeatedly , in which case you only get this benefit when using persistent connections.

[MarkR]

The "greater processing efficiency" benefit is largely a myth. It will only be true if the query is reused while it is prepared (which only lasts at a maximum, the length of the connection, i.e. one hit in most cases).

Also, MySQL has a query cache which is severely adversely affected by parameterised queries - if you use the parameterised query API (e.g. through PDO or mysqli), then it effectively bypasses the query cache.

The MySQL query cache caches complete queries and their entire results - it does this by checking the entire SQL statement - if it is exactly the same, it will then serve the results from the cache, without even parsing the statement. This is extremely good for web apps which often execute the exact same query again and again (MySQL automatically knows if a table has changed in the interim).

Parameterised queries can't use the main mysql query cache, hence can be a lot slower (They can still benefit from other optimisations, such as the tables already being open or the OS caching parts of the DB in RAM)

(NB: Above is approximately correct for MySQL 5.0, in all other versions results may vary; I expect a cleverer cache may be added sometime to a newer version).

Mark

[Roger_Ramjet]

Well, Mark, that is what I said - really only applies with persistent connections.

As to the query cache, it has to be the identical query while PS will accept different parameters so the 2 things would be used in entirely different cases.

Now one thing I am unclear on is how the cache will operate when selecting from a view in a prepared statement. Mysql will cache the view results and it seems to me that selecting from that view should be unaffected by the method used to select ie another query or a prepared statement.

I'll have to post in the mysql forums to clarify this unless someone here has an answer.

PS. queries using temp tables are not cached so in reality the cache only works with simple queries anyway

[ahundiak]

One really big problem (for me at least) with using prepared statements is lack of support for arrays for statements using IN ().

$ids = array(1,2,3);
$stmt = $db->prepare('SELECT * FROM table WHERE id IN(?)');

// No workee!
$stmt->query(array($ids));

This means that I have to manually escape arrays and build the queries with str_replace or whatever. Granted not all queries have an IN() but since many of mine do then I still need to fool with manual escaping. Very sad.

[Roger_Ramjet]

Well, if you really need all those queries with IN(), try this

$sql = 'SELECT * FROM table WHERE id IN(';
$c = count($ids);
for ($i=0; $i<$c; $i++) {
   $sql .= '?,';
}
$sql = substr($sql, 0, (strlen($sql)-1)) . ')';
$stmt = $db->prepare($sql);
$stmt->execute($ids);

Personally, if I found myself using a lot of IN()s then I would begin to think that there was something wrong with my db structure. Are you sure that this is not just a coding habit rather than a necessary solution?

[bpat1434]

This is all of course not to mention the SECURITY enhancements that you get with using prepared statements. Since you don't have to worry about escaping, the possibility of SQL injection is lowered.

I gotta get into PS now.... never really used them as I thought it limited you to mySQL 5+.....

[ahundiak]

Roger Ramjet wrote:

Well, if you really need all those queries with IN(), try this

Thanks but no thanks. Solution seems worse than the problem. Especially with multiple IN() clauses.

Roger Ramjet wrote:

Personally, if I found myself using a lot of IN()s then I would begin to think that there was something wrong with my db structure. Are you sure that this is not just a coding habit rather than a necessary solution?

Pretty sure it's not just a habit. Imagine you are trying to generate a game schedule with options for player ages, genders, leagues, fields, game types etc. Plenty of queries need to use IN().

[Roger_Ramjet]

Yes - IN(SELECT FROM WHERE) - or use a work table if the subquery was repeated several times.