Understanding Prepared Statements

Published on January 15, 2007

While working on my rewrite of Monkey Album, I ran into an interesting programming dilemma. In the past week or so, I’ve been introduced to the MySQLi extension in PHP. The current Monkey Album implementation makes use of the PHP 4 mysql_*() calls, so I thought I’d try out the MySQLi interface to see how it works.

MySQLi includes support for what are known as “prepared statements” (only available in MySQL 4.1 and later). A prepared statement basically gives you three advantages: (1) SQL logic is separated from the data being supplied, (2) incoming data is sanitized for you which increases security, and (3) performance is increased, since a given statement only needs to be parsed a single time.

It seems to me that the performance benefit can only be seen in situations where the query is executed multiple times (in a loop, for example). In fact, an article on prepared statements confirms this suspicion; the author in fact mentions that prepared statements can be slower for queries executed only once.

So here’s the problem I face: the queries that get executed in Monkey Album are, for the most part, only ever executed once. So, do I make use of prepared statements just to get the security benefit? It doesn’t seem worth it to me, since I can get the same security by escaping all user input (something I already do today). Does someone with more knowledge of this stuff have an opinion? If so, please share it.

Comments are closed.

Copyright © 2004-2019 Jonah Bishop. Hosted by DreamHost.