Should we always bind our SQL statements?

Question

I have been researching into PDO's bindValue(). I know that preparing my SQL statements with PDO is keeping SQL injections from happening.

Code Example:

$stmt = $dbh->prepare('SELECT * FROM articles WHERE id = :id AND title = :title');
$stmt->bindValue(':id', PDO::PARAM_INT);
$stmt->bindValue(':title', PDO::PARAM_STR);
$stmt->execute();

By binding the ID as a number, and the Title was a string, we can limit the damage done when someone tries to do an SQL injection within the code.

Should we always bind our values with a PDO::PARAM_ so we can limit what can be pulled from the database in an SQL injection? Does this add more security with PDO when doing our bindValue()?

Answer 1

There are two questions in one. It is essential not to confuse them

1. Should we always use a placeholder to represent a variable data in the query?
2. Should we always use certain function in the application code to follow the above rule?
   Also, from the clarification in the comments under the opening post, the third question can be seen:
3. Should we always use third parameter, or it's OK to let PDO bind all the parameters as strings by default?

1. For the first question the answer is absolutely and definitely - YES.

While for the second one, for sake of code sanity and DRYness -

2. Avoid manual binding when possible.

There are many ways to avoid manual binding. Some of them are:

• ORM is an excellent solution for the simple CRUD operations and must have in a modern app. It will hide SQL from you completely, doing the binding behind the scenes:

  $user = User::model()->findByPk($id);
  

• Query Builder is also the way to go, disguising SQL in some PHP operators but again hiding the binding behind the scenes:

  $user = $db->select('*')->from('users')->where('id = ?', $id)->fetch();
  

• some abstraction library may take care of the passed data by means of type-hinted-placeholders, hiding the actual binding again:

  $user = $db->getRow("SELECT * FROM users WHERE id =?i", $id);
  

• if you are still using PHP in the last century ways, and have raw PDO all over the code - then you can pass your variables in execute(), still saving yourself a lot of typing:

  $stmt = $dbh->prepare('SELECT * FROM users WHERE id = ?');
  $stmt->execute([$id]);
  $user = $stmt->fetch();
  

As of the third question - as long as you are binding numbers as strings (but not the opposite!) -

3. It's all right with mysql, to send almost every parameter as a string

as mysql will always convert your data to the proper type. The only case known to me, is a LIMIT clause where you cannot format number as a string - thus, the only related case is one when PDO is set in emulation mode and you have to pass a parameter in LIMIT clause. In all other cases you can omit third parameter, as well as explicit call to bindValue() without any problem.