Encrypt and decrypt text using the same key

Question

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.

Answer 1

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).

Answer 2

Try Casting the result after decryption try :

SELECT CAST(AES_DECRYPT(@C,@B) AS CHAR(50)) decrypt_value; 

Instead of :

SELECT AES_DECRYPT(@C,@B) as decrypt_value;