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

[Hendrik Visage]

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/20110311/2ee1fa43/attachment.htm