SEO

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

Brett Sinclair wrote:
> 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
>

Upgrade to IDS version 10.0, which will be out within 2 weeks. It has
column level encryption/decryption.

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 = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789";
let p_encrypt_set =
"NoPqRsTuVwXyZAbCdEfGhIjKlMnOpQrStUvWxYzaBcDeFgHiJ kLm5678901234";
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