SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 CHECKSUM replacement for IMAGE and TEXT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/22/2006 :  08:49:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.

This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.

With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.
CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
	@Data IMAGE
)
RETURNS INT
AS

BEGIN
	DECLARE	@Index INT,
		@MaxIndex INT,
		@SUM BIGINT,
		@Overflow TINYINT

	SELECT	@Index = 1,
		@MaxIndex = DATALENGTH(@Data),
		@SUM = 0

	WHILE @Index <= @MaxIndex
		SELECT	@SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
			@Overflow = @SUM / 4294967296,
			@SUM = @SUM - @Overflow * 4294967296,
			@SUM = @SUM ^ @Overflow,
			@Index = @Index + 1

	IF @SUM > 2147483647
		SET	@SUM = @SUM - 4294967296
	ELSE IF @SUM BETWEEN 32768 AND 65535
		SET	@SUM = @SUM - 65536
	ELSE IF @SUM BETWEEN 128 AND 255
		SET	@SUM = @SUM - 256

	RETURN @SUM
END
Actually this is an improvement of MS function, since it accepts TEXT and IMAGE data.
CREATE FUNCTION [dbo].[fnPesoTextChecksum]
(
	@Data TEXT
)
RETURNS INT
AS

BEGIN
	DECLARE	@Index INT,
		@MaxIndex INT,
		@SUM BIGINT,
		@Overflow TINYINT

	SELECT	@Index = 1,
		@MaxIndex = DATALENGTH(@Data),
		@SUM = 0

	WHILE @Index <= @MaxIndex
		SELECT	@SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
			@Overflow = @SUM / 4294967296,
			@SUM = @SUM - @Overflow * 4294967296,
			@SUM = @SUM ^ @Overflow,
			@Index = @Index + 1

	IF @SUM > 2147483647
		SET	@SUM = @SUM - 4294967296
	ELSE IF @SUM BETWEEN 32768 AND 65535
		SET	@SUM = @SUM - 65536
	ELSE IF @SUM BETWEEN 128 AND 255
		SET	@SUM = @SUM - 256

	RETURN	@SUM
END

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/14/2008 04:30:47

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/22/2006 :  18:18:14  Show Profile  Reply with Quote
Thumbs up, great work!

What compels a man to write a BINARY_CHECKSUM replacement ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/23/2006 :  00:43:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Just that I saw some responses how to differ between lower case and upper case when checking for login and passwords. BINARY_CHECKSUM is not that unique in it's response. I can with any seven characters create a checksum that matches any other checksum.

Could be a security issue too.

Anyhow, it seemed a good thing to do, to include TEXT and IMAGE with checksum operations.
And I had some spare time left.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/23/2006 04:13:18
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/23/2006 :  05:07:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here is how you break any BINARY_CHECKSUM.
Ie, how to create a string that produces the same checksum value as input.
CREATE FUNCTION dbo.fnPesoBreakChecksum
(
	@Checksum INT
)
RETURNS VARCHAR(69)
AS

BEGIN
	DECLARE	@Data VARCHAR(70),
		@Overflow TINYINT,
		@SUM BIGINT,
		@chk BIGINT,
		@Index TINYINT

	SET	@chk = @Checksum

	IF @Checksum BETWEEN -128 AND -1
		SET	@chk = @Checksum + 256
	ELSE IF @Checksum BETWEEN -32768 AND -1
		SET	@chk = @Checksum + 65536
	ELSE IF @Checksum < 0
		SET	@chk = @Checksum + 4294967296

	SELECT	@SUM = 268435456,
		@Index = 6,
		@Data = ''

	WHILE @Index > 0
		SELECT	@Overflow = @chk / @SUM,
			@Data = @Data +	CASE @Overflow
						WHEN 0 THEN ''
						ELSE '+CHAR(' + CONVERT(VARCHAR, 16 * @Overflow) + ')'
					END,
			@chk = @chk - @Overflow * @SUM,
			@SUM = @SUM / 16,
			@Index = @Index - 1

	IF @chk > 0
		SET	@Data = @Data + '+CHAR(' + CONVERT(VARCHAR, @chk) + ')'

	RETURN	SUBSTRING(@Data, 2, 69)
END
Call with SELECT dbo.fnPesoBreakChecksum(26435) --Checksum for 'abc'


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/14/2008 04:44:04
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/23/2006 :  10:01:33  Show Profile  Reply with Quote
Nice work Peter!




-ec
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/23/2006 :  18:12:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks!

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Naomi
Starting Member

USA
1 Posts

Posted - 10/31/2011 :  14:43:19  Show Profile  Reply with Quote
Take a look at this discussion
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0818cd0-cd84-43ad-9266-2c50c38affdf
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000