Log DBI query and values with placeholders

Hello all experts !

I need to log every INSERT, UPDATE and DELETE queries even when using
placeholders. Here some code:

$sql =3D "UPDATE users SET `name`=3D? WHERE =ECd`=3D1;";
$sth =3D $dbh->prepare($sql);
$sth->execute('Test User');
$sth->finish();

Of course execute params are given dynamically and I want to use
placeholders for more secure code.
I want to save that UPDATE query into file or database ( I'll prefer
DB :) ) for tracking purposes. Any idea how to do this?

Re: Log DBI query and values with placeholders

$sth->{Statement} returns the prepared statement. Using this as a base
You can get the used values in a HashRef by $boundParams =
$sth->{ParamValues} after an execute. I did not used it before ... but
it should work.

Regards

Robert

aspiritus schrieb:
> Hello all experts !
>
> I need to log every INSERT, UPDATE and DELETE queries even when using
> placeholders. Here some code:
>
> $sql = "UPDATE users SET `name`=? WHERE ìd`=1;";
> $sth = $dbh->prepare($sql);
> $sth->execute('Test User');
> $sth->finish();
>
> Of course execute params are given dynamically and I want to use
> placeholders for more secure code.
> I want to save that UPDATE query into file or database ( I'll prefer
> DB :) ) for tracking purposes. Any idea how to do this?
>
>

Re: Log DBI query and values with placeholders

aspiritus wrote:
> Hello all experts !
>
> I need to log every INSERT, UPDATE and DELETE queries even when using
> placeholders. Here some code:
>
> $sql = "UPDATE users SET `name`=? WHERE ìd`=1;";
> $sth = $dbh->prepare($sql);
> $sth->execute('Test User');
> $sth->finish();
>
> Of course execute params are given dynamically and I want to use
> placeholders for more secure code.
> I want to save that UPDATE query into file or database ( I'll prefer
> DB :) ) for tracking purposes. Any idea how to do this?
>
>

DBIx::Log4perl will do this and more. Log4perl supports logging to files
and databases and a lot more and DBIx::Log4perl can be inserted wherever
you use DBI very easily. Whether the format will be how you want it is
another question but as I have an interest in DBIx::Log4perl I would be
happy to hear your suggestions.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: Log DBI query and values with placeholders

I would have to agree with Martin that DBIx::Log4perl will work quite well.

In the long term you might want to accomplish this by adding some
triggers on your DB tables to record this data as well.

This way all changes to the table will be caught at the DB end. So in
cases where someone is able to log in and make changes in some other
manner, other than your perl code, are caught as well.

It will also save a great deal of coding.

The caveat being this does make the DB a little slower and you need
space to store all these changes.

cheers

Martin Evans wrote:
> aspiritus wrote:
>> Hello all experts !
>>
>> I need to log every INSERT, UPDATE and DELETE queries even when using
>> placeholders. Here some code:
>>
>> $sql = "UPDATE users SET `name`=? WHERE ìd`=1;";
>> $sth = $dbh->prepare($sql);
>> $sth->execute('Test User');
>> $sth->finish();
>>
>> Of course execute params are given dynamically and I want to use
>> placeholders for more secure code.
>> I want to save that UPDATE query into file or database ( I'll prefer
>> DB :) ) for tracking purposes. Any idea how to do this?
>>
>>
>
> DBIx::Log4perl will do this and more. Log4perl supports logging to
> files and databases and a lot more and DBIx::Log4perl can be inserted
> wherever you use DBI very easily. Whether the format will be how you
> want it is another question but as I have an interest in
> DBIx::Log4perl I would be happy to hear your suggestions.
>
> Martin

Re: Log DBI query and values with placeholders

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I need to log every INSERT, UPDATE and DELETE queries even when using
> placeholders. Here some code:
>
> $sql = "UPDATE users SET `name`=? WHERE d`=1;";
> $sth = $dbh->prepare($sql);
> $sth->execute('Test User');
> $sth->finish();
>
> Of course execute params are given dynamically and I want to use
> placeholders for more secure code.
> I want to save that UPDATE query into file or database ( I'll prefer
> DB :) ) for tracking purposes. Any idea how to do this?

The canonical way is to use the named tracing level 'SQL', like so:

$dbh->trace('SQL');

However, it's not supported on all DBDs yet (actually, DBD::Pg may be
the only one, but I'm not sure). From your non-standard use of backticks
above, I presume you are using DBD::mysql, which, from a quick glance
at the code, does not support 'SQL' tracing yet. Another option may be to
simply set a high numeric trace level: the queries will be logged, albeit
surrounded by a lot of noise (but parsing such things out is one of
the things Perl is good at, right?). You can also log such queries from
the database itself, of course (e.g. the db logs)

DBIx::Log4perl may work too, but it doesn't seem to mark the executes in
any way that tracks back to the statement that prepared them.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200804151151
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkgEz9YACgkQvJuQZxSWSsjl2ACgjbVeslZkMSJOS59b2HEK lYgi
OpEAoK6diZVTB2c2xj7rr6mIpeDn7vuC
=0WF3
-----END PGP SIGNATURE-----

Re: Log DBI query and values with placeholders

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> I need to log every INSERT, UPDATE and DELETE queries even when using
>> placeholders. Here some code:
>>
>> $sql = "UPDATE users SET `name`=? WHERE d`=1;";
>> $sth = $dbh->prepare($sql);
>> $sth->execute('Test User');
>> $sth->finish();
>>
>> Of course execute params are given dynamically and I want to use
>> placeholders for more secure code.
>> I want to save that UPDATE query into file or database ( I'll prefer
>> DB :) ) for tracking purposes. Any idea how to do this?
>
> The canonical way is to use the named tracing level 'SQL', like so:
>
> $dbh->trace('SQL');
>
> However, it's not supported on all DBDs yet (actually, DBD::Pg may be
> the only one, but I'm not sure). From your non-standard use of backticks
> above, I presume you are using DBD::mysql, which, from a quick glance
> at the code, does not support 'SQL' tracing yet. Another option may be to
> simply set a high numeric trace level: the queries will be logged, albeit
> surrounded by a lot of noise (but parsing such things out is one of
> the things Perl is good at, right?). You can also log such queries from
> the database itself, of course (e.g. the db logs)
>
> DBIx::Log4perl may work too, but it doesn't seem to mark the executes in
> any way that tracks back to the statement that prepared them.
>

That is a good point and I will correct that soon.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: Log DBI query and values with placeholders

On Apr 15, 3:10 pm, rrogg...@uni-osnabrueck.de (Robert Roggenbuck)
wrote:
> $sth->{Statement} returns the prepared statement. Using this as a base
> You can get the used values in a HashRef by $boundParams =3D
> $sth->{ParamValues} after an execute. I did not used it before ... but
> it should work.
>
> Regards
>
> Robert
>
> aspiritus schrieb:
>
> > Hello all experts !
>
> > I need to log every INSERT, UPDATE and DELETE queries even when using
> > placeholders. Here some code:
>
> > $sql =3D "UPDATE users SET `name`=3D? WHERE =ECd`=3D1;";
> > $sth =3D $dbh->prepare($sql);
> > $sth->execute('Test User');
> > $sth->finish();
>
> > Of course execute params are given dynamically and I want to use
> > placeholders for more secure code.
> > I want to save that UPDATE query into file or database ( I'll prefer
> > DB :) ) for tracking purposes. Any idea how to do this?

Thank you all for replays, here some thoughts:

I also want to record who did what and where, ex. who was the person
logged in and changed the username. This info is sent by HTTP_VARS on
each POST of form. My "log" contains these columns: id
(auto_increment), Name ( person who posted form), Timestamp,
Scriptname ( where form was posted),Sql ( actual query with
placeholders), Sql_values (executed values)


Keeping above in mind, Roberts idea makes sense and is best solution,
but I can't get $sth->{ParamValues} to work with mysql. $sth-
>{Statement} works just fine and I get column "Sql" populated.

Re: Log DBI query and values with placeholders

On Apr 16, 12:06 pm, lep...@gmail.com (Aspiritus) wrote:
> On Apr 15, 3:10 pm, rrogg...@uni-osnabrueck.de (Robert Roggenbuck)
> wrote:
>
>
>
> > $sth->{Statement} returns the prepared statement. Using this as a base
> > You can get the used values in a HashRef by $boundParams =3D
> > $sth->{ParamValues} after an execute. I did not used it before ... but
> > it should work.
>
> > Regards
>
> > Robert
>
> > aspiritus schrieb:
>
> > > Hello all experts !
>
> > > I need to log every INSERT, UPDATE and DELETE queries even when using
> > > placeholders. Here some code:
>
> > > $sql =3D "UPDATE users SET `name`=3D? WHERE =ECd`=3D1;";
> > > $sth =3D $dbh->prepare($sql);
> > > $sth->execute('Test User');
> > > $sth->finish();
>
> > > Of course execute params are given dynamically and I want to use
> > > placeholders for more secure code.
> > > I want to save that UPDATE query into file or database ( I'll prefer
> > > DB :) ) for tracking purposes. Any idea how to do this?
>
> Thank you all for replays, here some thoughts:
>
> I also want to record who did what and where, ex. who was the person
> logged in and changed the username. This info is sent by HTTP_VARS on
> each POST of form. My "log" contains these columns: id
> (auto_increment), Name ( person who posted form), Timestamp,
> Scriptname ( where form was posted),Sql ( actual query with
> placeholders), Sql_values (executed values)
>
> Keeping above in mind, Roberts idea makes sense and is best solution,
> but I can't get $sth->{ParamValues} to work with mysql. $sth-
>
> >{Statement} works just fine and I get column "Sql" populated.

Okay my friends !

Finaly I did get this to work: I needed to update my mysql-driver.
Version 3.0xx doesn't work !!

Thank you all for your help.