[syslog-ng] SQL destination function issue

[Balazs Scheidler]

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