• 8

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191


File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

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?


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!

      • 1
    • It is easy: If you know the string comes from your application and cannot be manipulated by a user, then there is no need for prepared statements, because there is nothing to inject. If you are not sure (bad, but in greater projects not avoidable) use prepared statement.

I think you want PDO::ATTR_EMULATE_PREPARES. That turns off native database prepared statements, but still allows query bindings to prevent sql injection and keep your sql tidy. From what I understand, PDO::MYSQL_ATTR_DIRECT_QUERY turns off query bindings completely.

  • 18
Reply Report

When not to use prepared statements? When you're only going to be running the statement once before the db connection goes away.

When not to use bound query parameters (which is really what most people use prepared statements to get)? I'm inclined to say "never" and I'd really like to say "never", but the reality is that most databases and some db abstraction layers have certain circumstances under which they won't allow you to bind parameters, so you're forced to not use them in those cases. Any other time, though, it will make your life simpler and your code more secure to use them.

I'm not familiar with PDO, but I'd bet it provides a mechanism for running parametrized queries with the values given in the same function call if you don't want to prepare, then run as a separate step. (e.g., Something like run_query("SELECT * FROM users WHERE id = ?", 1) or similar.)

Also, if you look under the hood, most db abstraction layers will prepare the query, then run it, even if you just tell it to execute a static SQL statement. So you're probably not saving a trip to the db by avoiding explicit prepares anyhow.

  • 15
Reply Report
    • PDO does not have such method. You have to use $dbh->prepare("SQL")->execute(array(PARAMETERS)); but then you can't get result from database. (PDOStatement::execute() returns only boolean (to indicate error).

Prepared statements are being used by thousands of people and are therefore well-tested (and thus one can infer they are reasonably secure). Your custom solution is only used by you.

The chance that your custom solution is insecure is pretty high. Use prepared statements. You have to maintain less code that way.

  • 9
Reply Report

The benefits of prepared statements are as follows:

  • each query is only compiled once
  • mysql will use a more efficient transport format to send data to the server

However, prepared statements only persist per connection. Unless you're using connection pooling, there would be no benefit if you're only doing one statement per page. Trivially simple queries would not benefit from the more efficient transport format, either.

Personally I wouldn't bother. The pseudo-prepared statements are likely to be useful for the safe variable quoting they presumably provide.

  • 7
Reply Report
    • Missed one, but not that one. Parameters to prepared statements are passed to the db server via a separate channel (i.e., not as part of an SQL statement), so they don't need to be escaped at all because they're explictly coming in as data, not as a potential command.
      • 1
    • It does not change anything in terms of efficiency, or it's a bit worse. SQL is compiled on the server, not on the client. So when you send a prepared statement with bound parameters you actually send multiple packets to the server (one for the prep stat, one for the parameters), thus it adds latency. However if you reuse your prep statement, then it's a gain because you save compilation time for the statement and only pay the penalty for sending the parameters.

Honestly, I don't think you should worry about it. However, I remember that a number of PHP data access frameworks supported prepare statement modes and non-prepare statement modes. If I remember correctly, PEAR:DB did back in the day.

I have ran into the same issue as you and I had my own reservations, so instead of using PDO I ended up writing my own light-weight database layer that supported prepares and standard statements and performed correct escaping (sql-injection prevention) in both cases. One of my other gripes with prepares is that sometimes it is more efficient to append some non-escapable input to a statement like ... WHERE id IN (1, 2, 3...).

I don't know enough about PDO to tell you what other options you have using it. However, I do know that PHP has escaping functions available for all database vendors it supports and you could roll your own little layer on top of any data access layer you are stuck with.

  • 2
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions