[Snort-users] How do I convert a snort source IP Number to IP address in Microsoft SQL Server
AJ Butcher, Information Systems and Computing
Alex.Butcher at ...11254...
Tue May 11 01:05:10 EDT 2004
--On 10 May 2004 16:20 -0700 Joe Stocker <jstocker101 at ...125...> wrote:
>> --On 04 May 2004 17:59 -0700 Joe Stocker <jstocker101 at ...125...> wrote:
>>> I am unable to convert the new snort database log format of an IP number
>>> from 2130706433 back to 127.0.0.1 using Microsoft SQL Server.
>>> I have been unable to find any examples except for MySQL and PostgreSQL.
>>> In MySQL you can use the inet_ntoa function: SELECT ip_src,
>>> inet_ntoa(ip_src) FROM iphdr;
>>> In PostgreSQL you can write your own function.
>>> But how would you write that function in Microsoft SQL?
>>> The ACID web page has a FAQ which describes how this should
>>> theoretically work:
>>> Let IP = the 32-bit unsigned integer representation of the IP address
>>> ip1 = octet 1 of 4 (high-order)
>>> ip2 = octet 2 of 4
>>> ip3 = octet 3 of 4
>>> ip4 = octet 4 of 4 (low-order)
>>> >> = bitwise shift right operator; takes an operand of the number
>>> bits to shift
>>> AND = bitwise AND operator
>>> ip1 = IP >> 24
>>> ip2 = (IP AND 00000000 11111111 00000000 00000000) >> 16
>>> ip3 = (IP AND 00000000 00000000 11111111 00000000) >> 8
>>> ip4 = (IP AND 00000000 00000000 00000000 11111111)
>>> IP = ip1 . ip2 . ip3 . ip4
>>> ***problem*** There is no >> operator in Microsoft SQL.
>>>> 8 is equivalent to * 256 (being 2^8)
>>>> 16 is equivalent to * 65536 (being 2^16)
>>>> 24 is equivalent to * 16777216 (being 2^24)
Oops, those should have been:
>> 8 is equivalent to / 256 (being 2^8)
>> 16 is equivalent to / 65536 (being 2^16)
>> 24 is equivalent to / 16777216 (being 2^24)
sorry for the confusion.
> I'm still stuck on how to exactly convert 2130706433 back to 127.0.0.1
I don't have any MS SQL servers I can test with, but based on the syntax
you posted, you should be able to do something like:
(select (convert(bigint,2130706433) & 4278190080) / 16777216 = 127
(select (convert(bigint,2130706433) & 16711680) / 65536 = 0
(select (convert(bigint,2130706433) & 65280) / 256 = 0
(select (convert(bigint,2130706433) & 255) / 1 = 1
To get the most significant octet through to least significant octet.
The bitwise AND operators are used to mask each byte of 2130706433 in turn.
More details at <http://www.sqlteam.com/item.asp?ItemID=331> (found by
googling for "microsoft sql bitwise") - note that it uses hex
representations of the masks, and uses power(2,n) instead to calculate the
decimal constants I use. Apart from being slightly less efficient, the
approach is exactly the same.
Alex Butcher: Security & Integrity, Personal Computer Systems Group
Information Systems and Computing GPG Key ID: F9B27DC9
GPG Fingerprint: D62A DD83 A0B8 D174 49C4 2849 832D 6C72 F9B2 7DC9
More information about the Snort-users