Calculate hash / checksum in Informix SQL
I would like to calculate some sort of hash or checksum value for a column value in Informix SQL.
Problem:
We need to anonymize personal information in the test data and would like to do this by hashing the corresponding values, for example:
UPDATE personal_data SET name=HASH(name), employee_no=HASH(employee_no)
We want to use a hash, not just a fixed value, because it is useful for different values (usually) for different results, and for the same values to match against the same result. This allows comparisons to be made across test data (for example, it is possible to have multiple records with the same employee_no, and it is useful to store this information).
Most DBMSs have some sort of hashing function (PostgreSQL and MySQL have MD5 (), Oracle has DBMS_UTILITY.GET_HASH_VALUE), but I couldn't find anything for Informix (Informix IDS 9). Is this a missing feature?
source to share
IDS does not have any hash functions shown as built-in AFAICR functions.
Assuming you are using IBM Informix Dynamic Server (IDS) version 10.00 or later (earlier versions are not supported - good, 7.31 is supported until 2009-09-30 but no further), then you can create a UDR (User Defined Procedure) that performs the task. It's not incredibly difficult - but it's not trivial either.
source to share
sleske,
Several years ago, I wrote a T-SQL function to compute SHA-1 hashes. If this works for you, perhaps you can redo my function for Informix. This news stream contains a little more information on restrictions.
create function S(
@N int,
@x bigint
) returns binary(4) as begin
declare @two_N bigint
set @two_N = power(cast(2 as bigint), @N)
declare @two_32_N bigint
set @two_32_N = power(cast(2 as bigint), 32-@N)
return cast(@x%@two_32_N*@two_N + @x/@two_32_N as binary(4))
end
go
create function f(
@t bigint,
@B bigint,
@C bigint,
@D bigint
) returns bigint as begin
declare @2_32 bigint set @2_32 = power(cast(2 as bigint),32)
if @t between 0 and 19
return (@B & @C) | ((@2_32-@B-1) & @D)
if @t between 20 and 39
return @B ^ @C ^ @D
if @t between 40 and 59
return (@B & @C) | (@B & @D) | (@C & @D)
return @B ^ @C ^ @D
end
go
create function SHA1 (
@s varchar(55)
) returns binary(20) as begin
declare @b varbinary(55)
set @b = cast(@s as varbinary(55))
declare @zeros binary(64)
set @zeros = 0x
declare @padded binary(64)
set @padded =
@b + 0x80 + substring(@zeros,1,55-datalength(@b))
+ cast(8*datalength(@b) as binary(8))
declare @H5 binary(20)
set @H5 = 0x67452301EFCDAB8998BADCFE10325476C3D2E1F0
declare @K4 binary(16)
set @K4 = 0x5A8279996ED9EBA18F1BBCDCCA62C1D6
declare @ABCDE binary(20) set @ABCDE = @H5
declare @W80 varbinary(320) set @W80 = @padded
declare @TEMP binary(4) set @TEMP = 0x
declare @2_32 bigint set @2_32 = power(cast(2 as bigint),32)
declare @t int
set @t = 16
while @t < 80 begin
set @W80 = @W80 +
dbo.S(1,cast(substring(@W80,(@t-3)*4+1,4) as bigint)
^cast(substring(@W80,(@t-8)*4+1,4) as bigint)
^cast(substring(@W80,(@t-14)*4+1,4) as bigint)
^cast(substring(@W80,(@t-16)*4+1,4) as bigint))
set @t = @t + 1
end
set @t = 0
while @t < 80 begin
set @TEMP = cast((cast(dbo.S(5,substring(@ABCDE,1,4)) as bigint)
+ dbo.f(@t,substring(@ABCDE,5,4)
,substring(@ABCDE,9,4)
,substring(@ABCDE,13,4))
+ cast(substring(@ABCDE,17,4) as bigint)
+ cast(substring(@W80,4*@t+1,4) as bigint)
+ cast(substring(@K4,4*(@t/20)+1,4) as bigint))%@2_32 as
binary(4))
set @ABCDE = @TEMP+substring(@ABCDE,1,4)
+dbo.S(30,substring(@ABCDE,5,4))
+substring(@ABCDE,9,8)
set @t = @t + 1
end
set @H5
= cast((cast(substring(@H5, 1,4) as bigint) + cast(substring(@ABCDE, 1,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5, 5,4) as bigint) + cast(substring(@ABCDE, 5,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5, 9,4) as bigint) + cast(substring(@ABCDE, 9,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5,13,4) as bigint) + cast(substring(@ABCDE,13,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5,17,4) as bigint) + cast(substring(@ABCDE,17,4)
as bigint))% @2_32 as binary(4))
return @H5
end
source to share
You can use ENCRYPT_TDES . The encrypted string is much longer than the original, but this shouldn't be a problem - the value will be truncated. Encryption + truncation should get the same result as the hash value. I'm not entirely sure if encryption is available at 9.40, maybe not. In this case, you need to compute the hash value on the client.
source to share
If you can change the structure of the table, you can use the WITH VERCOLS option. See Using the WITH VERCOLS option . In short: this option gives you two more hidden columns: the checksum for the originally inserted row and the number of changes (updates) in the row.
source to share