Why does the AND SQL operator make the query fail?

Question

I am learning the basics of PHP building a little web application.
In order to let it interface with the database, I am using a PDO instance.
All seems to work fine when I make queries with a single filtering condition, as:

SELECT * FROM mytable WHERE x = 1;

| id | x | y | z |
+----+---+---+---+
|  1 | 1 | a | 9 |

But when I add a second condition with the AND operator (for instance, AND y = 'a') something seems to not work properly, since no rows are returned.

Here is the PHP code with which I make the queries:

[...]

private $_PDO_TYPES = [
  'integer' => PDO::PARAM_INT,
  'string' => PDO::PARAM_STR,
];

private function _exec_query($query, $args) {
  $query_obj = $this->db_obj->prepare($query);
  foreach ($args as $key => $value) {
    $value_type = gettype($value);
    $pdo_type = $this->_PDO_TYPES[$value_type];
    $query_obj->bindParam($key, $value, $pdo_type);
  }
  $query_obj->execute();
  return $query_obj;
}

public function read_query($query, $args) {
  $query_obj = $this->_exec_query($query, $args);
  $rows = $query_obj->fetchAll(PDO::FETCH_ASSOC);
  return $rows;
}

[...]

$query = "SELECT * FROM mytable WHERE x = :x AND y = :y";
$rows = $dbi->read_query($query, [':x' => 1, ':y' => 'a']);
// $rows is an empty array...

Can you please explain me what I am missing to do the query in the right way? From command line, using mysql the query returns the correct table row.

Does your code work with just the the one parameter in the WHERE clause (without the AND)? — Progrock, Jan 02 '21 at 01:33
I think it is because $value is being overwritten (?) in the loop. You could try PDOStatement::bindValue(). https://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue — Progrock, Jan 02 '21 at 01:39
@Progrock, replying to your questions: 1. in the class constructor I set `$db_obj->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` 2. yes, with a single `WHERE` it seems to work. — rudicangiotti, Jan 02 '21 at 01:43
Try `$query_obj->bindParam($key, $args[$key], $pdo_type);` I think that should fix it — Nick, Jan 02 '21 at 01:58
@Progrock, replacing `bindParam()` with `bindValue()` solves the issue. — rudicangiotti, Jan 02 '21 at 02:02

Dharman · Accepted Answer · 2021-01-02T15:30:45.483

It is usually a very bad idea to bind PDO parameters based on the type of the variable in PHP. The binding type should be dictated by column definition or by SQL. It's much simpler to bind everything as strings and not worry about the correct cast type in bindParam().

Your actual problem comes from the fact that bindParam() binds by reference. Each time you iterate you overwrite the variables which means that you are creating a reference multiple times to the same value. What you want instead is binding by value.

There are two ways to achieve what you need.

Bind in execute.

private function _exec_query($query, $args)
{
    $query_obj = $this->db_obj->prepare($query);
    $query_obj->execute($args); // Pass arguments in execute
    return $query_obj;
}

Use bindValue().

private function _exec_query($query, $args)
{
    $query_obj = $this->db_obj->prepare($query);
    foreach ($args as $key => $value) {
        $query_obj->bindValue($key, $value);
    }
    $query_obj->execute();
    return $query_obj;
}

Why does the AND SQL operator make the query fail?

1 Answers1