Fix SQL Server Insert statements - escape single quotes.

I like the way you can configure kiwi sys-logger to dump to SQL Server - but why not automatically escape single quotes (i.e., replace with two single quotes) in the fields being inserted? Using ODBC and some sort of scripting (which seems to be the current workaround) is time consuming and unnecessary - wouldn't you always want to make sure that the insert statement isn't going to break because of un-escaped quotes?

Find more posts tagged with

statements

insert

sql

broken

server

Status: None

Comments

Aforsythe

I’m surprised I haven’t uploaded that script, I’ll have to dig for it and you can setup an all-messages rule at the top of the heap and remove the single quotes from every message before it gets to that point.

On topic though, I agree this should be done on the backend as a part of the log to db action.

roger.smith

Hi Acy,

We’ve had KIWI running here without issue for over a year – we were using it to dump the network device logs to SQL Server and it never had an issue.

A few weeks ago we started using it to do the same thing with the Domain Controllers (dumping the Windows Event Logs to SQL Server). We use te forwarding client supplied by the KIWI vendor…

It ran for a while then it stopped dumping to the SQL Host. Looking in the KIWI error log it looked like single quotes were not being escaped so I found a script in the examples supplied with KIWI, set up a DSN and managed to get it going again (script is below).

Now it runs for hours at a time, but then breaks again. I have found the easiest way to get it going again is to stop KIWI service, delete the queued files waiting to be dumped to SQL host, then start the KIWI service again. It seems to jam up every few hours and once it gets a log entry that fails to insert, it just gets jammed.

It’s a real pain for us as the logging is a requirement for Auditing and it won’t run for more than a few hours before it jams up again.

This is the script I’ve set up (I added the truncate as that seemed to be stopping it from working too.

If you’ve got any suggestions they would be much appreciated – the guys are talking about getting rid of KIWI – but I don’t think this can be too difficult to sort out at this stage – it’s almost working properly….

Kind regards,

Roger

Function Main()

Dim MyDSN

Dim SQLcmd

MyDSN = "DSN=xxxxxxxxxxxxxx;UID=yyyyyyyyyyyyy;PWD=zzzzzzzzzzzzzz;"

With Fields

SQLcmd = "INSERT INTO Syslogd (MsgDate,MsgTime,MsgPriority,MsgHostname,MsgText) VALUES (" & _

Quote(.VarDate) & "," & Quote(.VarTime) & "," & _

Quote(.ConvertPriorityToText(.VarPriority)) & "," & _

Quote(.VarPeerAddress) & "," & Quote(.VarCleanMessageText) & ")"

Call .ActionLogToODBC(MyDSN, MyTable, SQLcmd, 30)

End with

Main = "OK"

End function

Function Quote(Data)

Quote = "'" & Replace(LEFT(Data, 1024), "'", "''") & "'"

End Function