From a security stand point, what are the differences between these two queries?

[marcbraulio]

Hello everyone,

 

From a security stand point, what are the differences between these two queries?

 

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

 

and

 

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

 

Essentially they do the same thing, but does the addition of "Bind Parameters" add an extra layer of security?

I am currently using the latter method.

[requinix]

Possibly. The first one allows you to specify a data type manually while the second uses whatever type the variables are*. The first one provides a limit on string lengths but the second does not.

 

To match that,

$sth->execute(array(':calories' => (int)$calories, ':colour' => substr((string)$colour, 0, 12)));

 

* Note that values from GET and POST are strings!

[marcbraulio]

I see, thank you for the clear and precise answer!

[PFMaBiSmAd]

See the second half of the following reply for more information about passing an array to the ->execute() method - http://forums.phpfreaks.com/index.php?topic=363187.msg1718935#msg1718935

 

From the PDO ->execute() doc -

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

 

 

 

[requinix]

See the second half of the following reply for more information about passing an array to the ->execute() method - http://forums.phpfreaks.com/index.php?topic=363187.msg1718935#msg1718935

 

From the PDO ->execute() doc -

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

I knew I should have checked the manual first.