1

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.

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 3
    I'm voting to close this question as off-topic because it's not about database administration – mustaccio Feb 2 '18 at 13:25
  • 1
    @mustaccio questions don't have to be strictly about database administration as long as they fall within the scope of the site. If the presented code was Java or C# I would agree with you but this is a valid question. I vote to leave open. – Mr.Brownstone Feb 2 '18 at 15:14
  • @mustaccio - heaps of SQL questions aren't about database administration either but they're perfectly valid here - voting to leave open. It's actually quite an interesting one! – Vérace Oct 17 '18 at 11:49
  • @Vérace The question boils down to "please debug my code for me", without showing any effort on the OP's part. This would be off-topic anywhere on SE. – mustaccio Oct 17 '18 at 12:28
  • @mustaccio - now you've changed tack! :-) firstly it wasn't about db admin, now it's because the OP didn't put an attempt at the decode function? Maybe we could suggest to the OP that they try and do a basic decrypt function and post their efforts here? – Vérace Oct 17 '18 at 12:55
0

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

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.