Development

it’s all in how you look at it…

Not sure how to classify this bit of useless knowledge, but I’m putting it out there in case it sparks some-one’s creative interest.  Most of my day job entails parsing firewall syslog data; syslog may be a standard method of streaming data from a firewall, but there’s really very little standard about what is contained inside the syslog stream.  Each vendor has their own method of recording what events occurred on the firewall; if you’re trying to compare data between firewall vendors, it gets complicated.

Here’s a stream from a Watchguard (version 8.x) firewall:

firewalld[126]: deny out eth1 293 udp 20 127 192.168.10.211 192.9.202.3 137 137 (default)

Here’s one from a Cisco Pix:

%PIX-6-710005: UDP request discarded from 10.1.1.26/137 to inside:10.1.255.255/netbios-ns

Now, there are various methods of parsing the data; if you’ve ever used SQL Server to parse a message string, you know that it can get quite ugly.  Especially if the data is irregular.  Speaking of regularity, you can use regular expressions if you’re parsing the data with a client (such as VBScript or .NET), but it’s not always the easiest thing to do.  However, some of the firewall vendors (Watchguard,version 9) have recently become my friends (as in I don’t really know them, but I like what they’re doing), and adopted a quote-delimited standard for syslog traffic, like so:

disp="Deny"   pri="1" policy="Unhandled Internal Packet-00" src_ip="192.168.16.141" dst_ip="192.168.16.255" pr="netbios-dgm/udp" src_port="138" dst_port="138" src_intf="1-Trusted" dst_intf="Firebox"      rc="101" msg="denied" pckt_len="229" ttl="128"

Why do I like this so much?  Parsing in SQL is still a bear, and the streams may still be irregular (different events may have different attributes in different orders), so using VBScript and regular expressions haven’t really improved.  Look at the sample again; what happens if we slap a little extra information to the beginning and end?

<Msg disp="Deny"   pri="1" policy="Unhandled Internal Packet-00" src_ip="192.168.16.141" dst_ip="192.168.16.255" pr="netbios-dgm/udp" src_port="138" dst_port="138" src_intf="1-Trusted" dst_intf="Firebox"      rc="101" msg="denied" pckt_len="229" ttl="128" />

Suddenly, we have an XML fragment, which is MUCH easier to parse and manipulate, even in T-SQL (2005):

DECLARE @Msg XML
SET @Msg =‘<Msg disp="Deny" pri="1" policy="Unhandled Internal Packet-00" src_ip="192.168.16.141" dst_ip="192.168.16.255" pr="netbios-dgm/udp" src_port="138" dst_port="138" src_intf="1-Trusted" dst_intf="Firebox" rc="101" msg="denied" pckt_len="229" ttl="128" />‘


SELECT @Msg.value (‘(Msg/@disp)[1]‘,‘VARCHAR(50)‘)


I realize this is a very specific tip for a very select group of people, but I thought the exercise was worth pointing out; raw data may not always be in the format we want, but if it is standard and predictable, there may be simple methods of manipulating it.  XML is where you find it.

2008-12-04 by XML 0

Writing Dynamic SQL

I’m currently working on presentation for Dynamic SQL, and I thought I’d share one of my favorite tricks.  I had actually posted it to USENET earlier, so you can read the whole thread here, complete with some very useful suggestions, but here’s a synopsis:

As you’re probably aware, writing dynamic SQL statements can be a bear, and often involve breaking in and out of a concatenated string like so:

DECLARE @sql nvarchar(MAX)
SELECT @sql = N'SELECT * FROM '+ @DatabaseName
                + '.'+ @SchemaName
                + '.'+ @TableName
                + ' WHERE '+ @ColumnName + ' = ' + @Value
EXEC sp_executesql @sql

(Note that I am NOT endorsing the use of this example, or the cost or benefit of dynamic SQL as a whole; I’m simply showing a method of writing dynamic SQL when needed).

I use a REPLACE statement to avoid dinking around with the extra quotation marks, and thanks to suggestions by Erland and others (in the above thread), I can also mitigate some of the risk of SQL injection, like so:

SET @SQL = N'SELECT *
             FROM |DatabaseName|.|SchemaName|.|TableName|
             WHERE |ColumnName| = @param_value'

SET @SQL = REPLACE(@SQL,'|DatabaseName|',QUOTENAME(@DatabaseName))
SET @SQL = REPLACE(@SQL,'|SchemaName|',QUOTENAME(@SchemaName))
SET @SQL = REPLACE(@SQL,'|TableName|',QUOTENAME(@TableName))
SET @SQL = REPLACE(@SQL,'|ColumnName|',QUOTENAME(@ColumnName))

Happy coding!

2008-12-03 by SQL 1