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

[Balazs Scheidler]

started to do this port but got again distracted.

hopefully it can go into 3.3 first and then backport it to 3.2.

----- Original message -----
> On Fri, Mar 11, 2011 at 3:27 PM, Zoltán Pallagi <pzolee at balabit.hu>
> wrote:
> 
> > Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi
> > should port the fix back to OSE.
> > 
> 
> 3.2.2+ or will that go into 3.3alpha?
> 
> 
> > 
> > 
> > 
> > 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 INSERTstatement, 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>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/20110318/beb46e49/attachment-0001.htm