On Thu, 2009-12-17 at 11:43 -0500, Christopher O'Brien wrote:
Hi, I am trying to log messages from a Cisco ASA into a MySQL database through syslog-ng 3.0.4. I have the whole thing working except I cannot import data if I want to use a function. Here is an example:
Here is the log message that is coming into the syslog server from the ASA device:
Dec 04 2009 10:22:23: %ASA-5-305012: Teardown dynamic TCP translation from inside:10.6.42.166/2617 to outside:10.16.19.17/61631 duration 0:01:00
Here is what is logged by syslog-ng: Running SQL query; query='INSERT INTO stop (s_time, e_time, proto, in_ip, in_port, out_ip, out_port) VALUES (\'DATE_SUB(\"2009-12-04 10:22:23\", INTERVAL \"0:01:00\" HOUR_SECOND)\', \'2009-12-04 10:22:23\', \'TCP\', \'10.6.42.166\', \'2617\', \'10.16.19.17\', \'61631\')'
Here is what is recorded in the database: +-----+---------------------+---------------------+-------+-------------+---------+---------------+----------+ | idx | s_time | e_time | proto | in_ip | in_port | out_ip | out_port | +-----+---------------------+---------------------+-------+-------------+---------+---------------+----------+ | 166 | 0000-00-00 00:00:00 | 2009-12-04 10:22:23 | TCP | 10.6.42.166 | 2617 | 10.16.19.17 | 61631 |
Every field is recorded properly aside from the one that I am trying to use a function in. The purpose of the function is to record the start time of a translation. I get the end time from the syslog timestamp and the duration from the syslog message itself. I am trying to use a builtin MySQL function to record the start time in s_time. I think the problme is syslog-ng is putting single quotes around the whole function so it is being treated like a string literal by MySQL instead of variable data. Is that right? Is there a way to remedy this?
Here is my syslog-ng.conf configuration if it helps:
You are right, syslog-ng is only capable of inserting string literals into columns. Can you perhaps suggest a configuration file syntax how we could let syslog-ng know that a given column should not be enclosed in quotes? It would not be difficult to add support for this, once we come up with a configuration syntax, and take care about the escaping portion, because there is no single standard in how strings need to be escaped in SQL. e.g. if you have DATE_SUB() and you substitute strings in it, you need to care about escapes, otherwise you are risking SQL injection. -- Bazsi