CHECKSUM replacement for IMAGE and TEXT

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 ?

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

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