Help With Placeholders

Discussion in 'Perl Misc' started by PerlGoon, Jun 5, 2007.

  1. PerlGoon

    PerlGoon Guest

    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.
    PerlGoon, Jun 5, 2007
    #1
    1. Advertising

  2. PerlGoon

    Xicheng Jia Guest

    On Jun 4, 8:14 pm, PerlGoon <> wrote:
    > 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
    Xicheng Jia, Jun 5, 2007
    #2
    1. Advertising

  3. PerlGoon

    Bart Lateur Guest

    PerlGoon wrote:

    > 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.

    --
    Bart.
    Bart Lateur, Jun 5, 2007
    #3
  4. PerlGoon

    PerlGoon Guest

    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.
    PerlGoon, Jun 6, 2007
    #4
  5. PerlGoon

    Bart Lateur Guest

    PerlGoon wrote:

    >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

    --
    Bart.
    Bart Lateur, Jun 6, 2007
    #5
  6. * Bart Lateur <> wrote:
    >
    > PerlGoon wrote:
    >
    >>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


    Unfortunately not.

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

    --
    Lars Haugseth

    "If anyone disagrees with anything I say, I am quite prepared not only to
    retract it, but also to deny under oath that I ever said it." -Tom Lehrer
    Lars Haugseth, Jun 8, 2007
    #6
  7. PerlGoon

    Dr.Ruud Guest

    Lars Haugseth schreef:
    > Bart Lateur:
    >> PerlGoon:


    >>> 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

    >
    > 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".

    --
    Affijn, Ruud

    "Gewoon is een tijger."
    Dr.Ruud, Jun 9, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bill Belliveau
    Replies:
    0
    Views:
    359
    Bill Belliveau
    Aug 19, 2003
  2. Colin Basterfield

    PlaceHolders

    Colin Basterfield, Dec 23, 2003, in forum: ASP .Net
    Replies:
    5
    Views:
    595
    Colin Basterfield
    Dec 24, 2003
  3. DotNetGruven

    Nested PlaceHolders

    DotNetGruven, Mar 2, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    598
    Steven Cheng[MSFT]
    Mar 5, 2004
  4. buran
    Replies:
    1
    Views:
    407
    Bruno Sirianni
    Apr 6, 2004
  5. Josh
    Replies:
    1
    Views:
    9,519
    Jeffrey Palermo [MCP]
    May 26, 2004
Loading...

Share This Page