Help With Placeholders

[PerlGoon]

This question pertains to using DBI with a MySQL database.....

I use placeholders for all my queries (mainly for security). The
problem I have is that I need to be able to print the actual queries
my code runs into a log.... with the placeholders replaced.

For example, consider the following:

my $id = "example";
my $sql = "UPDATE Customers SET ID=?";
my $query = $db->prepare($sql);
$query->execute($id);

How do I print the ACTUAL query that was run into a log file.... with
the placeholders replaced???

For example, when I do the following:

print $sql;

It prints:

UPDATE Customers SET ID=?

When I need it to print:

UPDATE Customers SET ID='example'

I know that I could replace the placeholders myself... but then
again... I need to log the ACTUAL query that was just executed... or
attempted to be executed.

Any help is appreciated... and thank you in advance.

 

[Xicheng Jia]

This question pertains to using DBI with a MySQL database.....

I use placeholders for all my queries (mainly for security). The
problem I have is that I need to be able to print the actual queries
my code runs into a log.... with the placeholders replaced.

For example, consider the following:

my $id = "example";
my $sql = "UPDATE Customers SET ID=?";
my $query = $db->prepare($sql);
$query->execute($id);

How do I print the ACTUAL query that was run into a log file.... with
the placeholders replaced???

For example, when I do the following:

print $sql;

It prints:

UPDATE Customers SET ID=?

When I need it to print:

UPDATE Customers SET ID='example'

I know that I could replace the placeholders myself... but then
again... I need to log the ACTUAL query that was just executed... or
attempted to be executed.

How about this:

my $log_sql = $sql;
$log_sql =~ s{\?}{'$_'} for @values;
print $log_sql;

the array @values keeps all the binding values to the placeholders,
for your case, it's

my @values = qw($id);

you can pack this into a subroutine, i.e.

sub printSQL {
my $sql = shift;
print $sql and return if not @_;
$sql =~ s{\?}{'$_'} for @_;
print index($sql, '?') > 0 ? "need more binding values\n" : $sql;
}

(untested)

Regards,
Xicheng

 

[Bart Lateur]

The
problem I have is that I need to be able to print the actual queries
my code runs into a log.... with the placeholders replaced.

For example, consider the following:

my $id = "example";
my $sql = "UPDATE Customers SET ID=?";
my $query = $db->prepare($sql);
$query->execute($id);

How do I print the ACTUAL query that was run into a log file.... with
the placeholders replaced???

You can replace each placeholder in the SQL statement with a value
calculated as $dbh->quote($_) for the next bind value.

You might be able to do that by overriding execute and putting the
logging in that method, though I bet it will not be easy: DBI is a very
hard module to subclass.

 

[PerlGoon]

Thank you both for your help.

I was hoping there might be a method that I was overlooking so that I
didn't have to "redo" my queries by replacing the placeholders with
"quote($_)".

Instead I was looking for something already built into DBI that would
maybe return the last executed query... or something built into MySQL
that would log specific queries.

Again thank you for you help.

 

[Bart Lateur]

Instead I was looking for something already built into DBI that would
maybe return the last executed query... or something built into MySQL
that would log specific queries.

Set DBI trace?

http://search.cpan.org/perldoc?DBI#TRACING

 

[Lars Haugseth]

Set DBI trace?

http://search.cpan.org/perldoc?DBI#TRACING

Unfortunately not.

Quote from the "Trace Flags" subsection:
"SQL - trace SQL statements executed (not yet implemented)"

 

[Dr.Ruud]

Lars Haugseth schreef:

Bart Lateur:

Unfortunately not.

Quote from the "Trace Flags" subsection:
"SQL - trace SQL statements executed (not yet implemented)"

That is about "Trace Flags".

For me, this just works: $dbh->trace(1).
See also "RaiseError".