On Mon, May 20, 2013 at 10:27 AM, Charles Curley <[email protected]> wrote: > On Mon, 20 May 2013 09:34:22 -0600 > Lonnie Olson <[email protected]> wrote: > >> Using PDO gives you lots of other good things too. Especially >> prepared statements. These not only improve performance when executed >> multiple times, but gives you much better protection against SQL >> Injection attacks. It's also very easy to implement. >> http://us2.php.net/manual/en/pdo.prepared-statements.php >> >> ----------------------------- >> $query = $dbh->prepare("select rating, comment, categories from >> ratings where billType = ? and billNumber = ?"); >> $entry = $query->execute(array($billType,$billNumber)); > > I can see the elegance of prepared statements when you are planning on > re-using the statement with different parameters, say in order to stuff > a table for the first time. > > Is it all that useful in a web application, where usually you use a > statement once per execution of a given page and its backing script. > For example, I have an intake page for once bill. So to process the > bill, I look to see if there is already an entry for it. If there is, I > update it; if not I insert the bill. Each of those statements gets run > no more than one time in the script. Is it worth it to prepare a > statement for each of those? > > Part of that depends on where the prepared statement is stored. If it > is stored in the database, it might be worth it. If it is stored in the > PHP interpreter and lost when the script ends, it likely isn't. Also, > sqlite uses a local file, not a server daemon like PostGres. Even if > the prepared statement is stored in the database, would it be > persistent across calls to sqlite?
Prepared statements aren't "stored" anywhere. It's a mechanism to tell the DB server that this query will be run, so it can analyze and plan the query out. It's doesn't persist beyond the connection state. In your case of using it with SQLite, you may not benefit from any performance enhancing features, but you still benefit from SQL Injection prevention. Your original code uses string concatenation to build a query. This is very dangerous unless you have very, very, strict input validation. Prepared statements enforce that parameters are treated as only parameters and not SQL code. And it handles any needed quoting automatically. Also, it's very simple to implement, so even in the case of SQLite, it is still worth using it. /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
