Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When *not* to use prepared statements?

I'm re-engineering a PHP-driven web site which uses a minimal database. The original version used "pseudo-prepared-statements" (PHP functions which did quoting and parameter replacement) to prevent injection attacks and to separate database logic from page logic.

It seemed natural to replace these ad-hoc functions with an object which uses PDO and real prepared statements, but after doing my reading on them, I'm not so sure. PDO still seems like a great idea, but one of the primary selling points of prepared statements is being able to reuse them… which I never will. Here's my setup:

  • The statements are all trivially simple. Most are in the form SELECT foo,bar FROM baz WHERE quux = ? ORDER BY bar LIMIT 1. The most complex statement in the lot is simply three such selects joined together with UNION ALLs.
  • Each page hit executes at most one statement and executes it only once.
  • I'm in a hosted environment and therefore leery of slamming their servers by doing any "stress tests" personally.

Given that using prepared statements will, at minimum, double the number of database round-trips I'm making, am I better off avoiding them? Can I use PDO::MYSQL_ATTR_DIRECT_QUERY to avoid the overhead of multiple database trips while retaining the benefit of parametrization and injection defense? Or do the binary calls used by the prepared statement API perform well enough compared to executing non-prepared queries that I shouldn't worry about it?

EDIT:

Thanks for all the good advice, folks. This is one where I wish I could mark more than one answer as "accepted" — lots of different perspectives. Ultimately, though, I have to give rick his due… without his answer I would have blissfully gone off and done the completely Wrong Thing even after following everyone's advice. :-)

Emulated prepared statements it is!

like image 309
Ben Blank Avatar asked Feb 11 '09 04:02

Ben Blank


People also ask

When should I use prepared statements?

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements basically work like this: Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?").

Why do we use prepared statement instead of statement?

1. PreparedStatement in Java allows you to write a parameterized query that gives better performance than the Statement class in Java. 2. In the case of PreparedStatement, the Database uses an already compiled and defined access plan, this allows the prepared statement query to run faster than a normal query.

Do you need prepared statements for select?

You must always use prepared statements for any SQL query that would contain a PHP variable. To do so, always follow the below steps: Create a correct SQL SELECT statement.


1 Answers

Today's rule of software engineering: if it isn't going to do anything for you, don't use it.

like image 67
chaos Avatar answered Sep 20 '22 15:09

chaos