Use prepared statements everywhere in PHP? (PDO)

Question

I'm going to be switching my database class that I use in several sites/projects, from using a custom mysql_query method*, to using PDO and prepared statements. However I have a question first - do I want to use prepared statements everywhere? Even in places where the query will only be ran once? What about situations where I need to do something like:

INSERT INTO `table` (`column`, `column`) VALUES ('value','value'), ('value','value'),('value','value'), etc.

Should I use a single prepared statement (And a single VALUE), but execute it with different variables each time, or should I use the style above? If I do use a prepared statement here, how bad of a performance hit are we talking? Do I need to use transactions in this situation?

*My mysql_query method is similar to a prepared statement in that the user can call $mysql->Query("SELECT * FROM%sWHERE '%s'='%s'", $var, $var, $var), and the method auto-escapes everything with mysql_real_escape_string.

Answer 1

Prepared statements provide a good degree of protection against SQL injection, and they also provide a performance benefit for some types of query. Personally, I would use them everywhere.

If you discover that a particular query is causing performance problems, you can do some profiling to track down the cause of the problem, and then optimise the code or query as required. But don't try to micro-optimise before you have a problem.

As for transactions, just use them when you need them. For example, when you need to perform a sequence of all-or-nothing updates, where if one fails, the whole lot must fail. These can be useful for things like many-to-many relationships, where three tables must be updated, and you don't want partial relationships remaining if a failure occurs.

Answer 2

Use only PDO parameters to pass variables into the query.

You can use prepared statement for multiple insert as well:

$insertQuery = 'INSERT INTO table (col1, col2) VALUES ';
$insertQueryData = array();
$insertData = array();

foreach ($data as $record) {
  $insertQueryData[] = '(?, ?)';
  $insertData[] = $record['col1'];
  $insertData[] = $record['col2'];
}

$insertQuery .= implode(', ', $insertQueryData);

$statement = $db->prepare($insertQuery);
$statement->execute($insertData);

Answer 3

You should do the prepared statement every time. But, you may want to write a small helper that: prepares, binds, and runs, the query in one shot without multiple lines of code to do it.