[syslog-ng] escapes in SQL statements (was Re: dbtype in SQL logging)

Zoltán Pallagi pzolee at balabit.hu
Fri Mar 11 14:27:37 CET 2011 

Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi 
should port the fix back to OSE.


On 2011-03-11 13:35, Hendrik Visage wrote:
> Okay, now that I have the "" -> Null replacements, now I have another 
> escape issue.
>
> Making use of Postgresql's bigserial, it reads:
> <qoute 
> http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL>
> To insert the next value of the sequence into the serial column, 
> specify that the serial column should be assigned its default value. 
> This can be done either by excluding the column from the list of 
> columns in the INSERT statement, or through the use of the DEFAULT key 
> word.
> </quote>
>
> Which means I have to have *no* quotes, which the syslog-ng enforces :(
>
> destination d_pgsql {
>   sql(type(pgsql)
>   host("127.0.0.1") username("logwriter") password("logwriter") 
> port("5432")
>   database("syslog") table("SystemEvents")
> columns("ID bigserial primary key","ReceivedAt timestamptz NULL", 
> "DeviceReportedTime timestamptz NULL",
> "Facility smallint NULL","Priority smallint NULL","FromHost 
> varchar(60) NULL",
> "Message text","InfoUnitID int NULL","SysLogTag varchar(60)",
> "CustomerID bigint","NTSeverity int NULL","Importance int 
> NULL","EventSource varchar(60)","EventUser varchar(60) NULL",
> "EventCategory int NULL","EventID int NULL","EventBinaryData text 
> NULL","MaxAvailable int NULL","CurrUsage int NULL","MinUsage int NULL",
> "MaxUsage int NULL","EventLogType varchar(60)","GenericFileName 
> VarChar(60)","SystemID int NULL")
> values(DEFAULT,"$R_ISODATE", 
> "$S_ISODATE","$FACILITY_NUM","$LEVEL_NUM","$HOST",
> "$MSGONLY","1","$MSGHDR","","","","","","","","","","","","","","","")
> indexes("ID","ReceivedAt","Facility","Priority","FromHost","SysLogTag",)
> null(""));
> };
>
>
>
> Running SQL query; query='INSERT INTO SystemEvents (ID, ReceivedAt, 
> DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID, 
> SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser, 
> EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage, 
> MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES 
> (\'DEFAULT\', \'2011-03-11T14:14:12+02:00\', 
> \'2011-03-11T11:12:37+02:00\', \'20\', \'6\', \'betvmi01\', 
> \'[2011-03-11 11:12:37.565 11AA6B90 verbose \'\'App\'\'] [VpxaVMAP] 
> CheckThreshold percent changes (0,0,0,0) - threshold 5\', \'1\', 
> \'Vpxa: \', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
> NULL, NULL, NULL, NULL, NULL, NULL)'
> Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', 
> user='logwriter', database='syslog', error='*ERROR:  invalid input 
> syntax for integer: "DEFAULT"*\x0aLINE 1: ... EventLogType, 
> GenericFileName, SystemID) VALUES 
> (\'DEFAULT\',...\x0a                                                             
> ^\x0a', query='INSERT INTO SystemEvents (ID, ReceivedAt, 
> DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID, 
> SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser, 
> EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage, 
> MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES 
> (\'DEFAULT\', \'2011-03-11T14:14:12+02:00\', 
> \'2011-03-11T11:12:37+02:00\', \'20\', \'6\', \'betvmi01\', 
> \'[2011-03-11 11:12:37.565 11AA6B90 verbose \'\'App\'\'] [VpxaVMAP] 
> CheckThreshold percent changes (0,0,0,0) - threshold 5\', \'1\', 
> \'Vpxa: \', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
> NULL, NULL, NULL, NULL, NULL, NULL)'
>
> AS long as there is now escapes for the DEFAULT, then it "works":
>
> syslog=# INSERT INTO SystemEvents (ID, ReceivedAt, DeviceReportedTime, 
> Facility, Priority, FromHost, Message, InfoUnitID, SysLogTag, 
> CustomerID, NTSeverity, Importance, EventSource, EventUser, 
> EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage, 
> MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES 
> (DEFAULT, '2011-03-11T14:10:18+02:00', '2011-03-11T11:08:43+02:00', 
> '20', '6', 'betvmi01', '[2011-03-11 11:08:43.522 11921B90 verbose 
> ''App''] [VpxaVMAP::Invoke] Command returned successfully', '1', 
> 'Vpxa: ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
> NULL, NULL, NULL, NULL, NULL);
> INSERT 0 1
> syslog=#
>
> On Tue, Feb 22, 2011 at 12:59 PM, Zoltán Pallagi <pzolee at balabit.hu 
> <mailto:pzolee at balabit.hu>> wrote:
>
>     Try this:
>         columns("datetime varchar(16)", "host varchar(32)", "program
>     varchar(16)", "pid varchar(4) default null", "message varchar")
>           values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")
>
>             indexes("datetime", "host", "program", "pid", "message")
>       null(""));
>
>     null(): If the content of a column matches the string specified in
>     the /|null()|/ parameter, the contents of the column will be
>     replaced with an SQL NULL value
>
>     It worked for me:
>
>     Incoming log entry; line='<166>Feb 22 08:38:59 Vpxa: HostChanged
>     Event Fired, properties changed []'
>     Running SQL query; query='INSERT INTO
>     t2105010221cdb33661c82e91cb0b (datetime, host, program, pid,
>     message) VALUES (\'Feb 22 11:50:17\', \'thor-t410\', \'Vpxa\',
>     NULL, \'HostChanged Event Fired, properties changed []\')'
>
>     Ofc, you can use integer type for pid, but this is a string and
>     not an integer.
>
>
>     On 2011-02-22 10:57, Hendrik Visage wrote:
>>     Hi there,
>>
>>      Okay, looks like I was looking/thinking about something else, so
>>     lets explain my problem.
>>
>>      I want the pid inseted to be a "proper" number/integer, not a
>>     string/character, and the fun starts with several of my sources
>>     not having a pid entry, thus that field I would like to be filled
>>     with NULL values. However, it still gets escaped, and when it
>>     gets escaped, the NULL is not null anymore, but the string 'NULL' :(
>>
>>     I was hoping that in some why I could coerce syslog-ng to
>>     understand that that column is a integer column, thus should not
>>     be escaped as it is currently done ;(
>>
>>     I have the following destination:
>>     destination d_pgsql {
>>       sql(type(pgsql)
>>       host("127.0.0.1") username("logwriter") password("logwriter")
>>     port("5432")
>>       database("syslog")
>>       table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}") #or whatever
>>     you want, example ${HOST}" for hosts, ${LEVEL}" for levels.. etc
>>       columns("datetime varchar(16)", "host varchar(32)", "program
>>     varchar(16)", "pid varchar(8) default null", "message varchar")
>>       values("$R_DATE", "$HOST", "$PROGRAM", "${PID:-NULL}", "$MSG")
>>       indexes("datetime", "host", "program", "pid", "message"));
>>     };
>>
>>     and the following log lines:
>>
>>     Incoming log entry; line='<166>Feb 22 08:38:59 Vpxa: [2011-02-22
>>     08:38:59.862 1B7A7B90 verbose \'App\'] [VpxaHalServices]
>>     HostChanged Event Fired, properties changed []'
>>     Running SQL query; query='INSERT INTO logs_somedevice_20110222
>>     (datetime, host, program, pid, message) VALUES (\'Feb 22
>>     11:39:27\', \'somedevice\', \'Vpxa\', \'NULL\', \'[2011-02-22
>>     08:38:59.862 1B7A7B90 verbose \'\'App\'\'] [VpxaHalServices]
>>     HostChanged Event Fired, properties changed []\')'
>>
>>
>>     ______________________________________________________________________________
>>     Member info:https://lists.balabit.hu/mailman/listinfo/syslog-ng
>>     Documentation:
>>     http://www.balabit.com/support/documentation/?product=syslog-ng
>>     FAQ: http://www.campin.net/syslog-ng/faq.html
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.balabit.hu/pipermail/syslog-ng/attachments/20110311/493af415/attachment-0001.htm

More information about the syslog-ng mailing list