Column encryption

[skydvrz]

I am trying to encrypt several columns in a table with varying degrees of success, and could use some help. I can select and insert encrypted records, but am having problems coming up with a way to update existing records.

I am using the built-in MySQL aes_encrypt and aes_decrypt functions - they work fine in the SQL property of a TMyQuery. To make things more secure, I want to append a random "salt" value to a fixed password and use that to encrypt/decrypt the column values. This works:

Code: Select all

select
    y.id,
    aes_decrypt(password, concat(&pw, (select salt from ymembers where id =y.id))) as password,
    aes_decrypt(firstname, concat(&pw, (select salt from ymembers where id =y.id))) as firstname,
    aes_decrypt(middlename, concat(&pw, (select salt from ymembers where id =y.id))) as middlename,
    aes_decrypt(lastname, concat(&pw, (select salt from ymembers where id =y.id))) as lastname,
    salt
from ymembers y

The table looks like so:

Code: Select all

DROP TABLE IF EXISTS `contacts`.`ymembers`;
CREATE TABLE  `contacts`.`ymembers` (
  `id` int(11) NOT NULL auto_increment,
  `password` varchar(48) character set latin1 collate latin1_bin default NULL,
  `firstname` varchar(48) character set latin1 collate latin1_bin NOT NULL,
  `middlename` varchar(48) character set latin1 collate latin1_bin default NULL,
  `lastname` varchar(48) character set latin1 collate latin1_bin NOT NULL,
  `salt` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ind1` (`password`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4215808 kB';

My problem is with the TMyQuery.SQLUpdate property - I have to catch the query in BeforePost and update Salt with a new random value. I also have to concatenate the Salt string with the password and pass it to the DB engine to properly encrypt the updated column values.

I have tried using parameters and macros, but I think that in BeforePost, the SQL has already been parsed by MyDAC. How can I change the Salt value and pass it to the query?

If I try to update macro values in BeforePost, it crashes.

This SQLUpdate code does not work:

Code: Select all

UPDATE ymembers
SET
  password = aes_encrypt(:password,&ck), 
  firstname = aes_encrypt(:firstname,&ck), 
  middlename = aes_encrypt(:middlename,&ck), 
  lastname = aes_encrypt(:lastname,&ck),
  salt = :salt 
WHERE
  id = :Old_id

Any ideas?

[AndreyZ]

Hello,

To set the parameters of the insert, delete, update, lock, and refresh statements, you should use the BeforeUpdateExecute event handler. Here is an example:

Code: Select all

procedure TMainForm.MyQueryBeforeUpdateExecute(Sender: TCustomMyDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if stUpdate in StatementTypes then
    Params.ParamByName('salt').AsInteger := Random(1000);
end;