PHP PDO Prepare queries

Question

I read on PDO and I searched on StackOverFlow about pdo and prepare statement. I want to know what are/is the benefits or using the prepare statement. eg:

$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();

vs

$sql = "SELECT name, colour, calories FROM fruit WHERE calories < $calories AND colour = $colour";
$result = $connection->query($query);
$row = $result->fetch(PDO::FETCH_ASSOC);

both queries will return the same result so why using the prepare, for me it looks like it's gonna be slower since you have to execute an extra step.

thanks

Answer 1

Prepared statements are:

1. Safer: PDO or the underlying database library will take care of escaping the bound variables for you. You will never be vulnerable to SQL injection attacks if you always use prepared statements.
2. (Sometimes) Faster: many databases will cache the query plan for a prepared statement and refer to the prepared statement by a symbol instead of retransmitting the entire query text. This is most noticeable if you prepare a statement only once and then reuse the prepared statement object with different variables.

Of these two, #1 is far more important and makes prepared statements indispensable! If you didn't use prepared statements, the only sane thing would be to re-implement this feature in software. (As I've done several times when I was forced to use the mysql driver and couldn't use PDO.)

Answer 2

Prepare is faster when using a lot of queries (you already prepared the query) and it's more secure.

Your second code probably won't work - you're using parameters in a query but you're not defining them.

With query() you have to fill the query manually using quote() - this is more work and tends to make programmers careless.