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

Coherence

Your second example doesn't actually work--you didn't replace the parameters in the query with values! – Weathered

@FrancisAvila , well .. you could inject the correct values, but, yeah , by default it wont work. – Rockingham

In an earlier version of OP, $calories was just :calories. So yeah, my comment no longer applies. – Weathered

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.)

Weathered

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.

Cortez

And in your updated code you demonstrate why you mustn't use query(). Right now it's very easy to do a SQL injection, simply by changing $calories. Your prepare() example would've been injection proof. – Cortez

Answer 3

Prepare and binding parameters is meant to prevent sql injection,
is act likes escaping the variable before sending to database,
while your second query have no defense on that.

Fled

Answer 4

There is actually third option you missed :

$stmt = $dbh->prepare( '
   SELECT 
       name, 
       colour, 
       calories 
   FROM fruit 
   WHERE calories < :calories 
     AND colour = :colour
');
$stmt->bindParam( ':calories', $calories, PDO::PARAM_INT );
$stmt->bindParam( ':colour', $colour, PDO::PARAM_STR, 64 );
if ( $sth->execute() )
{
   $data = $sth->fetchAll( PDO::FETCH_ASSOC);
}

Maybe i am missing something , but setting cursor options seems a bit pointless , if you will end up doing fetchAll() anyway.

Katanga