PDO: Debugging prepared statements

When writing database code in PHP 4 I tended to use a querying
function that also provided some debugging support to echo the query
back to me if needed. For example:

function doQuery ($query)
// For queries that don't cause any changes to the database
{
if (CFG_DEBUG)
{
echo (htmlspecialchars ($query)."<br />\n");
}
if ($result = pg_query ($dbHandle, $query))
{
return (pg_fetch_all ($result));
}
}

with this if I define a CFG_DEBUG constant I can toggle debugging on
and off. Being able to echo back SQL statements as they are being
sent has been invaluable in debugging faulty SQL and in finding ways
to eliminate redundant queries and the like.

Now that we've got PHP 5 to play with, I want to use PDO for database
access, first and foremost for the prepared statement functionality.
Prepared Statements don't work the way doing queries the traditional
way of building up strings from user input does. While there's big
advantages to prepared statements (more secure against SQL injection,
speed advantages for repetitive queries), I can't echo the query being
executed back anymore.

Is there any way I can get at the query that's about to be executed as
a string with PDO prepared statements?

Gordon wrote:

> Is there any way I can get at the query that's about to be executed as a
> string with PDO prepared statements?

Nope.

You're using PostgreSQL, which supports prepared statements directly. Some
of the other databases supported by PDO (e.g. SQLite, MySQL<4.1) do not
support prepared statements, so PDO needs to emulate this functionality
internally.

With prepared statements, PHP just sends the database the template
statement, with placeholders for data, and then pumps the data in when you
execute the statement. PHP never sees a complete statement itself, so
there's no way that it can show it to you!

With emulated prepared statements, PDO itself combines the template
statement with the data before sending a complete query to the database.
In these cases, PHP would know what the complete statement was, so could
theoretically show it to you -- but you'd need to hack on the PDO module
(and in fact, probably on the database driver too) to find a way of
pulling it out.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 6 days, 21:00.]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/

On Dec 14, 10:02 am, Gordon <gordon.mc...@ntlworld.com> wrote:
> When writing database code in PHP 4 I tended to use a querying
> function that also provided some debugging support to echo the query
> back to me if needed. For example:
>
> function doQuery ($query)
> // For queries that don't cause any changes to the database
> {
> if (CFG_DEBUG)
> {
> echo (htmlspecialchars ($query)."<br />\n");
> }
> if ($result = pg_query ($dbHandle, $query))
> {
> return (pg_fetch_all ($result));
> }
> }
>
> with this if I define a CFG_DEBUG constant I can toggle debugging on
> and off. Being able to echo back SQL statements as they are being
> sent has been invaluable in debugging faulty SQL and in finding ways
> to eliminate redundant queries and the like.
>
> Now that we've got PHP 5 to play with, I want to use PDO for database
> access, first and foremost for the prepared statement functionality.
> Prepared Statements don't work the way doing queries the traditional
> way of building up strings from user input does. While there's big
> advantages to prepared statements (more secure against SQL injection,
> speed advantages for repetitive queries), I can't echo the query being
> executed back anymore.
>
> Is there any way I can get at the query that's about to be executed as
> a string with PDO prepared statements?

Do a var_dump on the statement, the query is a property on the
prepared statement, 'queryString' I think.

Regards.

Tim Hunt wrote:

> Do a var_dump on the statement, the query is a property on the prepared
> statement, 'queryString' I think.

$st->queryString still contains placeholders. It's not the final query.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 6 days, 21:56.]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/

Toby A Inkster wrote:

> With prepared statements, PHP just sends the database the template
> statement, with placeholders for data, and then pumps the data in when
> you execute the statement. PHP never sees a complete statement itself,
> so there's no way that it can show it to you!

You can kind of fake it though:

class MyPDOStatement extends PDOStatement
{
public $queryParams = NULL;

public function execute ($arr)
{
$this->queryParams = $arr;
return parent::execute($arr);
}

public function lastExecution ()
{
$rv = $this->queryString;
while (preg_match('/\?/', $rv))
$rv = preg_replace('/\?/','%'.(++$i).'$s',$rv,1);

$arr = $this->queryParams;
foreach ($arr as $k=>$v)
if (!is_numeric($v))
$arr[$k]="'".str_replace("'", "''", $v)."'";

return vsprintf($rv, $arr);
}
}
$pdo->setAttribute(PDO::ATTR_STATEMENT_CLASS, 'MyPDOStatement');

This only supports queries prepared with question mark parameters.
Supporting named parameters is an exercise left for the reader.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 6 days, 22:01.]

Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/

On Dec 14, 10:33 am, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Gordon wrote:
> > Is there any way I can get at the query that's about to be executed as a
> > string with PDO prepared statements?
>
> Nope.
>
> You're using PostgreSQL, which supports prepared statements directly. Some
> of the other databases supported by PDO (e.g. SQLite, MySQL<4.1) do not
> support prepared statements, so PDO needs to emulate this functionality
> internally.
>
> With prepared statements, PHP just sends the database the template
> statement, with placeholders for data, and then pumps the data in when you
> execute the statement. PHP never sees a complete statement itself, so
> there's no way that it can show it to you!
>
> With emulated prepared statements, PDO itself combines the template
> statement with the data before sending a complete query to the database.
> In these cases, PHP would know what the complete statement was, so could
> theoretically show it to you -- but you'd need to hack on the PDO module
> (and in fact, probably on the database driver too) to find a way of
> pulling it out.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 6 days, 21:00.]
>
> Sharing Music with Apple iTunes
> http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/

Good points, I've been using mysql which uses emulated prepared
staments by default.

You can use emulated prepared statments with postgres with $db-
>setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

The query string is available as a public property on the statement
with emulated statements, does this property not get set for other
drivers when emulated statements are used?

Regards

On Dec 14, 11:21 am, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Tim Hunt wrote:
> > Do a var_dump on the statement, the query is a property on the prepared
> > statement, 'queryString' I think.
>
> $st->queryString still contains placeholders. It's not the final query.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 6 days, 21:56.]
>
> Sharing Music with Apple iTunes
> http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/

Mmm thats true, it can still be useful for some sql debugging and even
more so with the statement class you posted.

Regards

On Dec 14, 10:33 am, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Gordon wrote:
> > Is there any way I can get at the query that's about to be executed as a
> > string with PDO prepared statements?
>
> Nope.

I suspected that would be the case, thanks.

On Dec 14, 11:40 am, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Toby A Inkster wrote:
> > With prepared statements, PHP just sends the database the template
> > statement, with placeholders for data, and then pumps the data in when
> > you execute the statement. PHP never sees a complete statement itself,
> > so there's no way that it can show it to you!
>
> You can kind of fake it though:
>
> class MyPDOStatement extends PDOStatement
> {
> public $queryParams = NULL;
>
> public function execute ($arr)
> {
> $this->queryParams = $arr;
> return parent::execute($arr);
> }
>
> public function lastExecution ()
> {
> $rv = $this->queryString;
> while (preg_match('/\?/', $rv))
> $rv = preg_replace('/\?/','%'.(++$i).'$s',$rv,1);
>
> $arr = $this->queryParams;
> foreach ($arr as $k=>$v)
> if (!is_numeric($v))
> $arr[$k]="'".str_replace("'", "''", $v)."'";
>
> return vsprintf($rv, $arr);
> }}
>
> $pdo->setAttribute(PDO::ATTR_STATEMENT_CLASS, 'MyPDOStatement');
>
> This only supports queries prepared with question mark parameters.
> Supporting named parameters is an exercise left for the reader.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 6 days, 22:01.]
>
> Sharing Music with Apple iTunes
> http://tobyinkster.co.uk/blog/2007/1...tunes-sharing/

You're a star Toby, thanks!