CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

aes_encrypt in MySql without using aes_encrypt in the sql

I'd like to do 

select name, lower(hex(aes_encrypt(name,'mykey'))) as encValue from myTable;

without using the part

lower(hex(aes_encrypt(name,'mykey'))) as encValue in the sql statement.

How can I do this? Can I somehow do the encrypting somewhere else, and still have it returned with the results? Maybe in a function? I'm doing this just to be on the safer side in case safety is compromised.

So my results will be:

name | encValue
me   | s63gd7dnd8dm

Upvotes: 0

Views: 523

Answers (1)

RandomSeed
RandomSeed

Reputation: 29809

You could create a function like this:

CREATE FUNCTION EncryptName(name VARCHAR(50)) -- use the same type as myTable.name
RETURNS VARCHAR(50)
RETURN LOWER(HEX(AES_ENCRYPT(name,'mykey')));

Then you can do things like this:

SELECT name, EncryptName(name) as encValue FROM myTable;

Make sure to protect this function definition from unwanted access. You are moving the security concern from the application layer to the database layer.

You may also create a view so as to mask even further the encryption process:

CREATE VIEW EncryptedView AS
SELECT name, LOWER(HEX(AES_ENCRYPT(name,'mykey'))) AS encValue FROM myTable;

Then you can use it as a regular table: SELECT * FROM EncryptedView. The same security concerns apply to the view definition.

Upvotes: 2

Related Questions