Re: encrypt/decrypt data before an update/insert
Brett Sinclair schrieb:
Hello Brett,
some time ago someone posted a stored procedure. Maybe this one is sufficient
for your purposes:
create procedure encrypt(_password varchar(255))
returning varchar(255);
define p_encrypt varchar(255);
define p_char_set char(62);
define p_char_help char(62);
define p_encrypt_set char(62);
define p_encrypt_help char(62);
define i smallint;
define j smallint;
define k smallint;
let p_char_set = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
let p_encrypt_set =
"NoPqRsTuVwXyZAbCdEfGhIjKlMnOpQrStUvWxYzaBcDeFgHiJkLm5678901234";
if (length(_password) > 0)
then let j = 1;
let k = length(_password);
let p_encrypt = "";
for i = 1 to length(_password)
let p_char_help = p_char_set;
let p_encrypt_help = p_encrypt_set;
for j = 1 to length(p_char_set)
if (_password[1] = p_char_help[1])
then let p_encrypt = p_encrypt_help[1] || p_encrypt;
let k = k - 1;
exit for;
end if;
let p_char_help = p_char_help[ 2,62];
let p_encrypt_help = p_encrypt_help[ 2,62];
end for;
if (j > length(p_char_set))
then let p_encrypt = null;
exit for;
end if;
let _password = _password[ 2,255];
end for;
end if;
return (p_encrypt);
end procedure;
The routine expects a password for encryption and uses a simple ROT13.
Calling "execute procedure encrypt('Informix')" should give "KVZEbsAv".
Use this as input and it should give you the original password (e.g.
"execute procedure encrypt(encrypt('Informix'))" should output "Informix").
> Hello everybody
> I'm looking for a way to create a stored procedure that will encrypt
> data in a column when doing an update or an insert.
> I would need also the decrypt version when doing a select of that
> column.
> I think I would put that call in a trigger so it would be transparent
> to the user.
>
> Anybody has this type of stored procedure ?
>
> Regards
> Brett
>
--
Roland Wintgen (Systemadministrator)
EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co KG
Trompeterallee 244-246, D-41189 Moenchengladbach
Tel. +49 21 66 / 55 08 23, Fax +49 21 66 / 55 08 90
www.evg.de rw@evg.de