Mysql – Encrypt and decrypt text using the same key

encryptionMySQL

I need to write a stored procedure that will accept two input string parameters and will encrypt the data as explained below.

First parameter should be the message to encrypt and second parameter should be the key for encryption. The encryption key must be min. 6 to 8 characters mix of alphabets & numbers. Procedure should perform encryption by adding the key to message. In addition to this, the procedure should also decrypt back the message using same key.

I am able to encrypt input text using a key, but not able to decrypt text using same key.

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_EncryptWithKey;
CREATE PROCEDURE `sp_EncryptWithKey`(IN `entext` varchar(50), IN `keyval` varchar(8))


begin
declare des varchar(500);
declare len int;
set len=length(keyval);
if keyval regexp '[A-Za-z]' && keyval regexp '[0-9]' && length(keyval)>=6 && length(keyval)<9

then


    begin

    SELECT entext,keyval INTO @A,@B;
    SET @C=AES_ENCRYPT(@A,@B);
    SELECT @C as encrypt_value;

    SELECT AES_DECRYPT(@C,@B) as decrypt_value;
    end;  
else
     select 'key length must be between 6-8' as error;



      end if;
end
$$

DELIMITER ;

In the stored procedure, if my input text is ABCD and key is pqrs123 then the output is encrypted text e.g. Ÿå4¸Wòm. If I give input text as Ÿå4¸Wòm with same key I should get ABCD as decrypted text.

Best Answer

Do not attempt to copy&paste the encrypted value. Do not try to put the encrypted value in any kind of TEXT field, only BLOB (or BINARY).

Related Question