lists.openwall.net   lists  /  announce  owl-users  owl-dev  john-users  john-dev  passwdqc-users  yescrypt  popa3d-users  /  oss-security  kernel-hardening  musl  sabotage  tlsify  passwords  /  crypt-dev  xvendor  /  Bugtraq  Full-Disclosure  linux-kernel  linux-netdev  linux-ext4  linux-hardening  linux-cve-announce  PHC 
Open Source and information security mailing list archives
 
Hash Suite: Windows password security audit tool. GUI, reports in PDF.
[<prev] [next>] [day] [month] [year] [list]
Message-ID: <F9C0B32C4FFE7147BD0FF6A40BE806E701AE5A@Hammer_Exchange.hammerofgod.com>
Date: Wed, 16 Jan 2008 12:20:15 -0800
From: "Thor (Hammer of God)" <thor@...merofgod.com>
To: <bugtraq@...urityfocus.com>
Subject: SQL scalar function to convert big int to dot notation

For those of you logging ISA (or whatever) to SQL, you'll have no doubt
noted that the source and destination IP's are logged as long integers,
and not dot notation.   While this is great for anyone using geo-ip data
for reporting (as in using "between" logic in your queries) it's not
human readable.

For convenience, I've coded up a little scalar function for SQL in order
for you to easily convert the long int IP to dot notation for anyone
interested.  Once the function is created, you can simply do a "Select
blah, blah, blah, dbo.ConvertLongIP(IntSourceIPHere),
dbo.ConvertLongIP(IntDestinationIPHere)  from logs where whatever =
whatever" to return the dot notation IP. 

The SQL to create the function is included below.

Thanks.
t





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		Thor
-- Create date: 11/28/07
-- Description:	Scalar function to converts long/bigint formatted IP
addresses to dot notation
-- =============================================
ALTER FUNCTION [dbo].[ConvertLongIP] 
(
	-- Add the parameters for the function here
	@LongIP bigint
)
RETURNS varchar(15)
AS
BEGIN

DECLARE @DotIP varchar(15),
		@bin varbinary(4)
 select @bin = cast(@LongIP as varbinary(4))
 select @DotIP = cast(convert(int,substring(@bin,1,1)) as varchar(3)) +
'.' 
 + cast(convert(int,substring(@bin,2,1)) as varchar(3)) + '.' 
 + cast(convert(int,substring(@bin,3,1)) as varchar(3)) + '.' 
 + cast(convert(int,substring(@bin,4,1)) as varchar(3))

RETURN @DotIP

END

Powered by blists - more mailing lists

Powered by Openwall GNU/*/Linux Powered by OpenVZ