http://qs321.pair.com?node_id=1144719

LanX has asked for the wisdom of the Perl Monks concerning the following question:

Honourable Monks,

For security reasons I'm trying to convince colleagues to start using placeholders in SQL prepared statements instead of using variable interpolation ( I don't want to discuss this further)

Now I'm getting the requirement to be able to show and log the resulting SQL statement of an ->execute() .

The motivation is to facilitate a cut&paste into an SQL GUI in case of problems.

There are multiple workarounds I could think of to mimic the process of ->bind_param() , but is there a proper way to use DBI.pm to get the analogous query after ->execute() ?

for instance this

my $statement = <<'__SQL__'; SELECT * FROM t_user_portal WHERE f_client = ? __SQL__ my $sth = $dbh->prepare($statement) or die $dbh->errstr; my $client='lanx'; my $rv = $sth->execute($client) or die $sth->errstr;

showing

SELECT * FROM t_user_portal WHERE f_client = 'lanx'

in case of problems?

I'm aware that MySql doesn't compose the query with placeholders by concatenating strings, it's rather something like:

PREPARE stmt1 FROM 'SELECT * FROM t_user_portal WHERE f_client = ?'; SET @client = 'lanx'; EXECUTE stmt1 USING @client;

which is still a acceptable for debugging (and easily constructable as a workaround).

So my question is:

Does DBI.pm show any resulting SQL-code from ->bind_param() or do I need to reconstruct the last code by myself after an error occurred?

Cheers Rolf
(addicted to the Perl Programming Language and ☆☆☆☆ :)
Je suis Charlie!

Replies are listed 'Best First'.
Re: DBI.pm: composing and debugging MySql placeholders
by neilwatson (Priest) on Oct 13, 2015 at 16:05 UTC
      Thanks!

      I experimented, and the following

      { local $sth->{TraceLevel} = "2|SQL"; $rv = $sth->execute(@args) or die $sth->errstr; }

      Produced the desired output, unfortunately surrounded by some verbose lines (see second paragraph). Reducing the trace level eliminated the information.

      DBI::st=HASH(0x2910b18) trace level set to 0x100/2 (DBI @ 0x0/0) i +n DBI 1.623-ithread (pid 7420) -> execute for DBD::mysql::st (DBI::st=HASH(0x2910ab8)~0x2910b18 ' +xLangsdorf') thr#309f18 Called: dbd_bind_ph -> dbd_st_execute for 02497600 >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets mysql_st_internal_execute MYSQL_VERSION_ID 50147 >parse_params statement SELECT * FROM ksr_bf_utf8.t_user_portal WHERE f_client = ? Binding parameters: SELECT * FROM ksr_bf_utf8.t_user_portal WHERE f_client = 'xLangsdorf' <- dbd_st_execute returning imp_sth->row_num 19 <- execute= ( 19 ) [1 items] at placeholder.pl line 57 -> STORE for DBD::mysql::st (DBI::st=HASH(0x2910b18)~INNER 'TraceL +evel' 0) thr#309f18 -> dbd_st_STORE_attrib for 02497600, key TraceLevel <- dbd_st_STORE_attrib for 02497600, result 0 <- STORE= ( 1 ) [1 items] at placeholder.pl line 56

      So I'd need to redirect the trace to a variable and parse the output for a paragraph starting with 'Binding parameters: ' in case of error...

      something like

      open my $trace_fh, ">", \ my $trace_out; { $sth->trace("2|SQL", $trace_fh ); $rv = $sth->execute(@args) or die parse($trace_out) . "\n" .$sth->errstr; }

      Thanks again! :)

      Cheers Rolf
      (addicted to the Perl Programming Language and ☆☆☆☆ :)
      Je suis Charlie!

Re: DBI.pm: composing and debugging MySql placeholders
by mje (Curate) on Oct 13, 2015 at 16:40 UTC

    Look at DBIx-Log4perl or DBIx-LogAny for a way to get placeholders from the statement handle e.g., ParamValues and ParamTypes. Both of those modules can log SQL and placeholders. Look at DBI callbacks and you can intercept the execute and do methods.

    If you actually want to recreate the SQL with the placeholders in place that is slightly more difficult but not impossible

      > If you actually want to recreate the SQL with the placeholders in place that is slightly more difficult but not impossible

      Well automatically constructing the following should be straight forward, (though I'm not sure about namespaces in SQL, but for cut and paste this should be irrelevant)

      PREPARE stmt1 FROM 'SELECT * FROM t_user_portal WHERE f_client = ?'; SET @client = 'lanx'; EXECUTE stmt1 USING @client;

      Cheers Rolf
      (addicted to the Perl Programming Language and ☆☆☆☆ :)
      Je suis Charlie!

Re: DBI.pm: composing and debugging MySql placeholders
by karlgoethebier (Abbot) on Oct 13, 2015 at 17:38 UTC

    I hope you don't mind if i guess once more.

    As far as i remember with MSSQL you can see the "real" queries with net start MSSQL$SQLEXPRESS /T4032.

    And then in Management Studio you say dbcc traceon(3605, -1) and can look at the queries in C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG or what ever you named it.

    Using MySQL the command SET GLOBAL general_log = 'ON'; should IMHO have a similar effect.

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»