[syslog-ng] SQL destination function issue

[Christopher O'Brien]

Balazs Scheidler wrote:
> 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.
> 

Thanks for the reply.  I'm not entirely sure what would be the
appropriate way to approach this.  When I was trying to get it to work I
thought something like the template-escape function might apply but I
don't think I fully understand what that does still.

What about an option that you could apply to specific columns and
specify an escape character to use.  Then if that escape character is
used in the values() portion of the destination configuration, the
insert function would be escaped and interpreted instead of being added
as a string literal.

For example, take my configuration.  Say I wanted to use a function in
the two columns, s_time and e_time for arguments sake.  The function
could look something like:

escape("<LIST OF COLUMNS>", "<ESCAPE CHARACTER>");
escape("s_time,e_time", "|");

So if I surrounded those fields in my insert statement with |, the
function could be interpreted.  I would assume there would have to be
some background code to differentiate between database types as well.

I'm not sure if that's what you were asking for as far as suggestions
go, but I figured I would give it a shot.  Thanks for considering my
concern.
-Chris