CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Zerquix18
Zerquix18

Reputation: 769

Should I bind internal values when preparing queries in PDO?

Having the following code

<?php
$age = get_age(); // a generated or random unsigned integer
$db->prepare("SELECT * FROM Customers WHERE name = :name AND age = :age");
$db->bindValue('name', $_POST['name']);
$db->bindValue('age',  $age, PDO::PARAM_INT);
$db->execute();

Should I bind 'age' even if it doesn't come from the user (i.e outside)?

If I have a fixed age or it's somehow generated by me, should I just do this?

<?php
$age = get_age(); // <- Of course I made sure this is an unsigned integer
$db->prepare("SELECT * FROM Customers WHERE name = :name AND age = $age");
$db->bindValue('name', $_POST['name']);
$db->execute();

Are there pros and cons for each or is it just personal preference?

Upvotes: 3

Views: 410

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157919

Should I bind "internal" values when preparing queries in PDO?

Yes.

You should understand the difference between a silly artificial example and a mature real life code during its lifetime span.

If you want to learn how to write a mature code, consider the following:

  • there is a thing called a consistency. Your first example looks consistent while second is not. After all, it's just a simple rule to follow - every variable should go into query via placeholder. Why hinder your development and puzzle yourself pondering on exceptions?
  • every application tends to grow and evolve. The source of $age may be changed in the future.
  • not every code is just a spaghetti bowl with SQL queries run on the spot. Some programmers being able to grasp the separation of concerns concept, making their application modular and flexible. In such an application, at the time of a query execution a database layer has absolutely no clue where the data is coming from, whether it's "safe" or not nor what does it "safe" mean at all.
  • all this binding hassle is not about "external" variables. But just for the query integrity. Whatever "internal" value could just have incorrect format to be placed in the query directly. Imagine your function fails and returns an empty string. It will result in an SQL error in the second case. You have to understand that you are binding your values not for Bobby Tables but for Sarah O'Hara.

Upvotes: 6

keupsonite
keupsonite

Reputation: 399

This is just a personal preference, in this case. And I think this is better for understanding the query when this is a big query. But, if a day someone change the function get_age() to retrieve the data by user, this could be dangerous.

Upvotes: 0

Dominic Harvey
Dominic Harvey

Reputation: 73

Yes, you need to bind in your context due to the usage parametter in your sql statement (see: http://php.net/manual/fr/pdostatement.bindvalue.php).

Alternatively, you could do:

<?php
$age = get_age(); // <- Of course I made sure this is an unsigned integer
$sth= $db->prepare("SELECT * FROM Customers WHERE name = ? AND age = ?");
$values = array("john", $age);
$sth->execute($values);

Upvotes: 1

Related Questions