[syslog-ng] escapes in SQL statements (was Re: dbtype in SQL logging)
Balazs Scheidler
bazsi at balabit.hu
Mon Mar 21 23:08:21 CET 2011
On Fri, 2011-03-11 at 14:27 +0100, Zoltán Pallagi wrote:
> Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi
> should port the fix back to OSE.
>
I bit the bullet and tried to extract all SQL related fixes from the PE
source tree.
It is right now on top of syslog-ng-3.3.git, probably backport is not
impossible, but I didn't do that, as I'm going to sleep now.
Also, I find it important to mention, that the PE team of syslog-ng has
started to migrate the PE product to the OSE core, as outlined some time
ago, and their current state is published on git.balabit.hu:
http://git.balabit.hu/?p=syslog-ng-team/syslog-ng-core-4.1.git;a=summary
The complete PE product has not yet been migrated, this is
work-in-progress, but you can watch that git tree. They tend to rebase
it from time-to-time, so don't fork your personal repository from there,
or be prepared to rebase when they do.
I've ported this patch:
http://git.balabit.hu/?p=syslog-ng-team/syslog-ng-core-4.1.git;a=commit;h=325e30cad4d3791b1bff6632b91a07c1ed3a522b
And these are the results (in the OSE repo):
377745f93575bb6e2a2b807599de8a503e10ec4d afsql: don't let the user configure the sql() destination without columns/values/indexes optio
c868c74696bbe4de03bfe8a7fa28d74a2fbd388f afsql: use an md5 hashed name for the index name for Oracle
e87333100f35bd3b982c5398efe8aaeebd8f47b1 afsql: use database specific BEGIN TRANSACTION queries
4aaf55bbbb3413f6680f1c00bb6d5c3f7c9727a1 afsql: add support for SQL-schema defined default values in columns
b05d39c4af90dd9fc8b16827e0dc6248779710dd afsql: make the number of attempts to insert a message runtime configurable
Please let me know if had any success.
Thanks.
>
> 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>
> > 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
> >
> >
> >
>
> ______________________________________________________________________________
> 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
>
--
Bazsi
More information about the syslog-ng
mailing list