PDO for MySQL has several major advantages over directly using MySQLi.
- PDO supports more than one database backend. This helps should one of your clients request a port of your application to a different database.
- Prepared statements in PDO support named placeholders in addition to the positional (
?) placeholders that MySQLi supports.
- PDO supports binding a single parameter at a time instead of all parameters at once. This allows stepping through an array of arguments and binding each, instead of having to construct a type string and then use
call_user_func_array() black magic.
- PDO supports a convenient shortcut by passing an array to
$stmt->execute(), where each key's value is bound to the placeholder as the key. (Caveat: It casts each value to a string, so you have to bind one at a time if your statement has a variable LIMIT. This is documented, and there's a feature request to change this casting to string.)
Items 2 through 4 combine to make it much easier to express the right side of operator IN in a provably injection-safe manner. With PDO, you can build an associative array with sequential names (such as [':likeval0'=>$val0, ':likeval1'=>$val1, ':likeval2'=>$val2]) and then safely build a placeholder list on the right side out of that array. Code might look like this:
$args = [];
foreach ($usernames as $n=>$value) {
$args[":likeval$n"] = $value;
}
$list = implode(',', array_keys($args));
// result is like ':likeval0,:likeval1,:likeval2'
$stmt = "SELECT * FROM app_users WHERE `username` IN ($list)";
$stmt = $dbh->prepare($stmt);
$c = $stmt->execute($args);
MySQLi supports only ? placeholders and all-at-once binding through a variable argument function call. In MySQLi, constructing the right side of operator IN is actually a lot easier by using $dbh->escape_string() for each element in a list. This approach is safe against SQL injection if well tested but raises red flags with some "Bobby Tables" parameterization purists.
