Problem keeping archive of all queries - Cannot write string to MySQL 1

[southbeach]

I am trying to track all queries ever ran in my application. Since all queries are executed via UDF named DoQuery($sql) (where $sql is the MySQL command construct), this is a very simple change - Except, I can't get it to work.

Here is what I have:

$trackthis = 'INSERT INTO queries VALUES (`query`,`user`) VALUES (' . mysql_escape_string($sql) . ',"' . $_SESSION['who_user'] . '")';

The queries table consists of the following:
id int(9) auto_increment primary nonull
date timestamp on update current_time_stamp nonull
query text nonull
user varchar(10) nonull

I know that the $sql variable will have quotes within as part of the string value, but notice I am using mysql_escape_string().

It just does not get much simpler than this. What am I missing?

 

[jpadie]

could you try this ?

$query = mysql_real_escape_string($sql);
$trackthis = "Insert into queries (id, `date`, `query`, `user`) values (null, now(), '%s','%s)";
mysql_query(sprintf($trackthis, $query, $_SESSION['who_user'])) or die (mysql_error());

i think the problem is just your use of quotes. you have not enquote the query string.

 

[southbeach]

I am getting error ... I added a couple of echo commands so that I can see content of variables.

The code looks like this:

$query = mysql_real_escape_string($sql);
$trackthis = "Insert into queries (id, `date`, `query`, `user`) values (null, now(), '%s','%s)";
echo $query . '<br />';
echo $trackthis . '<br />';

mysql_query(sprintf($trackthis, $query, $_SESSION['who_user'])) or die (mysql_error());

The content and error are:

SELECT a.*, b.* FROM products a, usercustable b \r\nWHERE a.prodBranch = \'MIA\' \r\nAND a.ProdCustID = b.ucCustID \r\nAND b.ucUserID = 13 ORDER BY prodDesc
Insert into queries (id, `date`, `query`, `user`) values (null, now(), '%s','%s)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''13)' at line 1

Notice I just added the echo commands to your snipped. I did this after having received the error, I figure it would help to review the text string.

 

[southbeach]

OK - missing single quote ... fixed and it works!

Thank you so very much ...

Why was it failing the way I had it? I thought that if I had the fields id as auto_increment and date as on update ..., there was no need to include within the insert construct as they would default automatically.

Thanks,

 

[jpadie]

the problem was this i think

$trackthis = 'INSERT INTO queries VALUES (`query`,`user`) VALUES ([red]\'[/red]' . mysql_escape_string($sql) . '[red]\'[/red],"' . $_SESSION['who_user'] . '")';

 

[DonQuichote]

If you can configure the database instance (probably not if it runs at your provider), you can just switch on the query log in my.cnf. I can recommend that for all development machines (but be careful keeping it switched on in a live environment).

If you use a tail program (for example, baretail on Windows or multitail on Linux), you see all queries pass as you are testing the site.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)