How To Use Mysql Functions In Active Record?

[festsoft (David Johnson)]

Hi

I am a relative newcomer to Yii but I am having difficuly in how to implement something like

SELECT item1, item2, AES_DECRYPT(name, key), … FROM table WHERE …

Perhaps it goes in CDbCriteria select property, but I cannot figure out the syntax to include the function.

Any help would be much appreciated!

[festsoft (David Johnson)]

Hi

I am a relative newcomer to Yii but I am having difficuly in how to implement something like

SELECT item1, item2, AES_DECRYPT(name, key), … FROM table WHERE …

Perhaps it goes in CDbCriteria select property, but I cannot figure out the syntax to include the function.

Any help would be much appreciated!

I think I have now got it to work:

$criteria->select = “item1, item2, AES_DECRYPT(name, ‘key’) AS real_name, …”;

together with $real_name being declared as a public propery of the model.

Isn’t Yii marvellous!

[shness]

$criteria->select = “item1, item2, AES_DECRYPT(name, ‘key’) AS real_name, …”;

I am just wondering about the security of that query. By decrypting your data in the query, your query has the key in it. What types of logs will have that key? Usually, logs for selects are turned off, but it would be very easy to get the key by turning them on.

You might want to do the encryption and decryption on the PHP side. You can lock those scripts down away from a dba.

From a performance perspective I wonder if the crypto is faster or on the PHP side or the MySQL side. Not sure. Of course, this may not be important, and in that case, ignore my rant.