[PHP] Get actual SQL query from prepared statement

[boogerjones]

I have the following code:

// $db is a PDO object connecting to a MySQL server using the MySQL Native Driver on PHP 5.3.2 running on Ubuntu
$stmt= $db->prepare("SELECT passhash FROM users WHERE username=:user LIMIT 1");
$stmt->bindParam(":user", $username);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

How do I get the actual SQL query that gets prepared and/or executed (in other words, the result of placing the escaped $username variable into the SQL query text? Obviously I could escape the SQL myself, but that would obviate the benefits of using prepared statements. I looked through the PHP docs and was surprised that this kind of functionality isn't built in; it would be a very useful debug tool.

I'm compiling PHP from source, so I suppose I could theoretically hack such a function in there myself, but that sounds dangerous.

[Hot]

Just print out the prepared object. That should tell you how it's storing the statement (and thus how you could access it).

print_r($stmt);

[boogerjones]

Close, but that just prints out the same text that I put into the prepare() function. I'm guessing this is simply the fundamental nature of prepared statements; maybe the actual SQL doesn't get constructed until it hits the database server.

[Hot]

Hmm, try printing it after you execute then.

[boogerjones]

  On 13/05/2010 at 02:34, Hot said:

Hmm, try printing it after you execute then.

I tried it at every step. It's the same each time. Maybe it works with MySQLi; I'll test that. It could be a native driver issue, too, but I'd have to recompile the PHP source tree to get rid of that.

[boogerjones]

Nope, doesn't work with mysqli either. I just get this from doing print_r($stmt):

mysqli_stmt Object
(
    [affected_rows] => -1
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 2
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)

With PDO, I get this:

PDOStatement Object
(
    [queryString] => SELECT passhash, badlogincount FROM users WHERE username=:user LIMIT 1
)