Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 CHECKSUM replacement for IMAGE and TEXT

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 08:49:34
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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-22 : 18:18:14
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

30421 Posts

Posted - 2006-08-23 : 00:43:11
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 05:07:16
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
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-23 : 10:01:33
Nice work Peter!




-ec
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 18:12:12
Thanks!

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Naomi
Starting Member

1 Post

Posted - 2011-10-31 : 14:43:19
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
   

- Advertisement -