Getting the url run by PDO when NOT using bindparam?

Question

For many queries, it's faster and easier to just use the question-mark notation for a query. There are various guides and posts about getting the final run query by extending PDO, but I never see one that works when using the question-mark notation. Can this be done? I'm running a query that appears by all accounts to work, but it's not returning results in the actual PDO code for some reason. What does it take to actually get the EXACT final query that PDO is running so I can see where the error is?

$sth = $dbh->prepare("SELECT fromID,toID,m_key FROM messages WHERE (fromID = ? AND toID IN (?)) OR (toID = ? AND fromID IN (?)) AND message LIKE 'addfriend'");
$sth->execute(array($_SESSION['userID'],$include,$_SESSION['userID'],$include));

If emulate prepares is `false` that *is* the final query sent to mysql. — Mike, Sep 07 '16 at 02:48
It's not that it's "faster and easier" but that it's safer and cleaner. — tadman, Sep 07 '16 at 03:39
you can see this answer: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — luoboss, Sep 07 '16 at 05:36

score 2 · Answer 1 · answered Sep 07 '16 at 02:47

2

The problem is not with the use of the ? placeholder, but the fact that you try to bind a single variable to represent a list of variables in the in operator. You have to provide as many ? placeholders in the in operator separated by commas as the number of parameters you want to have there and you need to bind to each placeholder separately.

...fromID IN (?, ?, ?, ..., ?)...

answered Sep 07 '16 at 02:47

Shadow

33,525
10
51
64

Assuming `$include` is actually list of values, which... who knows? – Mike Sep 07 '16 at 02:51
1

Pray, tell me, what else would it be? – Shadow Sep 07 '16 at 04:15
It could be literally anything; a string containing comma separated values, a single value, an object, an array, null. I don't see it being defined anywhere. – Mike Sep 07 '16 at 15:05
The php data type is not indicated, however, the `in` sql operator expects a list of parameters separated by comma. The OP has a single `?` placeholder there. Technically, `in` works with one parameter, but that does not make too much sense. The most logical assumption is that the OP tries to bind a list of parameters, but all of them go into a single variable. Since the OP only complains about no records being returned, the php data type can't really be array or object, otherwise a php error would have been raised. – Shadow Sep 07 '16 at 15:29
I realized that after some searching, though thank you for clarifying. That said, I can't accept your answer because what I'm looking for specifically is how to get the query that PDO is using. – not_a_generic_user Sep 07 '16 at 21:37
Turn on the mysql query log, run the prepared statement and check the query log for the final query. With prepared statements the query and the parameters are sent separately to the server, PDO cannot show you the full query with the parameters interpolated into it. Just do not forget to turn off the query log afterwards. – Shadow Sep 08 '16 at 02:28

score 1 · Answer 2 · answered Sep 07 '16 at 05:43

The below method will help you to substitute the value ($params) against ? or :param placeholder. So that we can see the exact query to be executed.

Note: It is useful only for development debugging purpose.

   public static function interpolateQuery($query, $params) {
        $keys = array();
        $values = $params;

        # build a regular expression for each parameter
        foreach ($params as $key => $value) {
            if (is_string($key)) {
                $keys[] = '/:'.$key.'/';
            } else {
                $keys[] = '/[?]/';
            }

            if (is_array($value))
                $values[$key] = implode(',', $value);

            if (is_null($value))
                $values[$key] = 'NULL';
        }
        // Walk the array to see if we can add single-quotes to strings
        array_walk($values, create_function('&$v, $k', 'if (!is_numeric($v) && $v!="NULL") $v = "\'".$v."\'";'));

        $query = preg_replace($keys, $values, $query, 1, $count);

        return $query;
    }

Is there really no way for the PDO to return the exact and final query that it creates and sends to the DB? Must it always be emulated? — not_a_generic_user, Sep 07 '16 at 21:40
@not_a_generic_user : Yes i think. I also surfed a lot and i can't find thats why i used this method to get final query to be executed. — Tamil, Sep 08 '16 at 05:14

Getting the url run by PDO when NOT using bindparam?

2 Answers2