CODEWITHSUNDEEP
phpmysqlsecuritypdosql-injection
Nicholas Mordecai
Nicholas Mordecai

Reputation: 889

Which PDO bind approach should be used for greater security?

I know of two ways to use PDO in PHP to update a MySQL database record. Please could someone explain which one I should use for better security and the difference and I am a little confused.

Method One:

$user = "root";
$pass = "";
$dbh = new PDO('mysql:host=somehost;dbname=somedb', $user, $pass);
$sql = "UPDATE coupons SET 
coupon_code = :coupon_code, 
valid_from = :valid_from, 
valid_to = :valid_to,  
discount_percentage = :discount_percentage,  
discount_amount = :discount_amount,  
calculationType = :calculationType,  
limit = :limit  
WHERE coupon_code = :coupon";
$stmt = $dbh->prepare($sql);                                  
$stmt->bindParam(':coupon_code', $_POST['coupon_code'], PDO::PARAM_STR);       
$stmt->bindParam(':valid_from', $_POST['$valid_from'], PDO::PARAM_STR);    
$stmt->bindParam(':valid_to', $_POST['valid_to'], PDO::PARAM_STR);
$stmt->bindParam(':discount_percentage', $_POST['discount_percentage'], PDO::PARAM_STR); 
$stmt->bindParam(':discount_amount', $_POST['discount_amount'], PDO::PARAM_STR);   
$stmt->bindParam(':calculationType', $_POST['calculationType'], PDO::PARAM_STR);   
$stmt->bindParam(':limit', $_POST['limit'], PDO::PARAM_STR);   
$stmt->bindParam(':coupon', $_POST['coupon_code'], PDO::PARAM_STR);   
$stmt->execute();

Method Two:

$dbtype="somedbtype";
$dbhost="somehost";
$dbname="somedb";
$dbuser="someuser";
$dbpass= "somepass";
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
$title = 'PHP Pattern';
$author = 'Imanda';
$id = 3;
$sql = "UPDATE books 
SET title=?, author=?
WHERE id=?";
$q = $conn->prepare($sql);
$q->execute(array($title,$author,$id));

From what I can see, method two does not bind the data, rather insert it directly into the query as an array type. Does this make the script more susceptible to SQL injection or other security risks?

Upvotes: 2

Views: 126

Answers (2)

developerwjk
developerwjk

Reputation: 8659

The only difference between the two is that if you pass the array in to the execute function rather than calling bindParam yourself, it treats all parameters as PDO::PARAM_STR automatically, whereas in calling bindParam yourself you could bind them as integers, etc.

From the docs:

input_parameters

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.

You can also see from the examples there that you can use named parameters (e.g. :limit) when passing the array into the execute function. You don't have to just put ?. In that case you give the array a key:

$sth->execute(array(':calories' => $calories, ':colour' => $colour));

Upvotes: 4

Shane Lessard
Shane Lessard

Reputation: 655

It's mostly a matter of preference. Both protect you from injection.

Though I think it's much easier to force data type with bind(), where as using execute(array()) will be using strings.

Upvotes: 1

Related Questions