Hi Guys,

Attached is a small extension project containing a couple of functions that:

1. Return a MD5 hash of a string, and
2. Compare a MD5 hash of a string with an existing hash value

I would really like to see a more elegant way of passing the column values
to the function, however, I understand it is not possible at this time. Any
comments/thoughts welcome.

The extension uses an Indy hashing routine that should come with and be
compatible with all versions of Delphi.

I use hashing to stamp records in critical/sensitive tables and then
determine whether any unauthorised changes have been made. I have previously
done the hashing etc. in the application code, however, I believe the use of
an extension function is more transparent and more secure.

Hope you find it useful

Best regards

Steve

Hi Guys,

The following example script will create INSERT and UPDATE triggers that use
the HashString() function ..

EXECUTE IMMEDIATE 'CREATE TRIGGER "InsertRecordHash" BEFORE INSERT ON "User"
BEGIN
DECLARE UserGUID VARCHAR;
DECLARE LastName VARCHAR(20);
DECLARE FirstName VARCHAR(20);
DECLARE UserName VARCHAR(20);
DECLARE Email VARCHAR(250);
DECLARE Hash VARCHAR(32);

SET UserGUID = COALESCE(NEWROW.UserGUID, '''');
SET LastName = COALESCE(NEWROW.LastName, '''');
SET FirstName = COALESCE(NEWROW.FirstName, '''');
SET UserName = COALESCE(NEWROW.UserName, '''');
SET Email = COALESCE(NEWROW.Email, '''');

EXECUTE IMMEDIATE ''SELECT HashString('''''' + UserGUID + LastName +
FirstName + UserName + Email + '''''') INTO ? FROM User'' USING Hash;

SET NEWROW.RecordHash = Hash;
END
';

EXECUTE IMMEDIATE 'CREATE TRIGGER "UpdateRecordHash" BEFORE UPDATE  OF
"UserGUID", "LastName", "FirstName", "UserName", "Email" ON "User"
BEGIN
DECLARE UserGUID VARCHAR;
DECLARE LastName VARCHAR(20);
DECLARE FirstName VARCHAR(20);
DECLARE UserName VARCHAR(20);
DECLARE Email VARCHAR(250);
DECLARE Hash VARCHAR(32);

SET UserGUID = COALESCE(NEWROW.UserGUID, '''');
SET LastName = COALESCE(NEWROW.LastName, '''');
SET FirstName = COALESCE(NEWROW.FirstName, '''');
SET UserName = COALESCE(NEWROW.UserName, '''');
SET Email = COALESCE(NEWROW.Email, '''');

EXECUTE IMMEDIATE ''SELECT HashString('''''' + UserGUID + LastName +
FirstName + UserName + Email + '''''') INTO ? FROM User'' USING Hash;

SET NEWROW.RecordHash = Hash;
END
';

Best regards

Steve


"Steve Forbes" <ozmosysspamfree@optusnet.com.au> wrote in message
news:9685247A-B511-46F3-9287-CF40050902BF@news.elevatesoft.com...

Steve,

<< I would really like to see a more elegant way of passing the column
values to the function, however, I understand it is not possible at this
time. Any comments/thoughts welcome. >>

Sounds like a good candidate for a new SQL function.

--
Tim Young
Elevate Software
www.elevatesoft.com

Nice! Might use this someday!

"Steve Forbes" <ozmosysspamfree@optusnet.com.au> wrote in message
news:9685247A-B511-46F3-9287-CF40050902BF@news.elevatesoft.com...
> Hi Guys,
>
> Attached is a small extension project containing a couple of functions
> that:
>
> 1. Return a MD5 hash of a string, and
> 2. Compare a MD5 hash of a string with an existing hash value
>
> I would really like to see a more elegant way of passing the column values
> to the function, however, I understand it is not possible at this time.
> Any comments/thoughts welcome.
>
> The extension uses an Indy hashing routine that should come with and be
> compatible with all versions of Delphi.
>
> I use hashing to stamp records in critical/sensitive tables and then
> determine whether any unauthorised changes have been made. I have
> previously done the hashing etc. in the application code, however, I
> believe the use of an extension function is more transparent and more
> secure.
>
> Hope you find it useful
>
> Best regards
>
> Steve
>
>
>