Re: How do I convert a snort source IP Number to IP address in Microsoft SQL Server

["AJ Butcher, Information Systems and Computing" <Alex.Butcher () bristol ac uk>]

--On 10 May 2004 16:20 -0700 Joe Stocker <jstocker101 () hotmail com> wrote:

> > --On 04 May 2004 17:59 -0700 Joe Stocker <jstocker101 () hotmail com> 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:
> > > http://acidlab.sourceforge.net/acid_faq.html#faq_e1
> > > 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
> > >
> > > Then,
> > >    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.

Best Regards,
Alex.
--
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




-------------------------------------------------------
This SF.Net email is sponsored by Sleepycat Software
Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to 
deliver higher performing products faster, at low TCO.
http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users