How to check the integrity of a file in SQL Server 2005 inserted into a BLOB - varbinary(max)?

Question

I'm trying to do a check for a file uploaded to a varbinary column in SQL Server 2005.

I uploaded the file and using

SELECT DATALENGTH(thefile) FROM table

I get the same number of bytes that the file has.

CHECKSUM is not the better way and HASHBYTES only takes the first 8000 bytes, and my files are so more great than that.

I can only use T-SQL.

Any tip will be helpful.

Thanks a lot :)

Answer 1

You can use hash over hash, it has the same strength as single-pass hash:

CREATE FUNCTION dbo.GetMyLongHash(@data VARBINARY(MAX))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @res VARBINARY(MAX) = 0x
    DECLARE @position INT = 1, @len INT = DATALENGTH(@data)

    WHILE 1 = 1
    BEGIN
        SET @res = @res + HASHBYTES('MD5', SUBSTRING(@data, @position, 8000))
        SET @position = @position+8000
        IF @Position > @len 
          BREAK
    END
    RETURN @res
END
GO

declare @theHash varbinary(max)

select @theHash = dbo.GetMyLongHash(thefile) from table

WHILE DATALENGTH(@thehash) > 16 SET @TheHash = dbo.GetMyLongHash(@theHash)

Of course you can modify the function for returning already final-pass hash