Search code examples
mysqlvb.netencryption

Incorrect parameter count in the call to native function 'aes_decrypt'

I am trying to move our encryption from Code to the database to speed things up. When I attempt to decrypt the information using this select statement I get an incorrect parameter count error.

SELECT AES_DECRYPT(u.strFirstName,'usa2010') FROM EncryptingTest.tblUser u;

I've looked at the documentation and this should work. Can someone tell me what I am doing wrong??

EDIT I have tried Restarting the MySQL Server to no avail. The server Version is 5.6.22

Solution

  • From the example you provided it seems ok. Can you see about casting it first?

     SELECT CAST(AES_DECRYPT(u.strFirstName,'usa2010') AS CHAR(50)) FROM EncryptingTest.tblUser u;

    Please check this link it has some great resources on the issue your facing...

    http://mysqlblog.fivefarmers.com/2014/03/27/mysql-5-6-17-now-with-better-encryption/

    EDIT - The actual fix

    Using AES_ENCRYPT() or AES_DECRYPT() with block_encryption_mode set to a block cipher other than ECB will produce an error if the IV is not provided:

    mysql> SET @@session.block_encryption_mode = 'aes-256-cbc';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'aes_encrypt'
mysql> SELECT HEX(AES_ENCRYPT('test', 'key', RANDOM_BYTES(16)));
+---------------------------------------------------+
| HEX(AES_ENCRYPT('test', 'key', RANDOM_BYTES(16))) |
+---------------------------------------------------+
| 2EFBA8708925C1DF8B661E57938FAE5E                  |
+---------------------------------------------------+
1 row in set (0.00 sec)
Note that the IV isn’t itself stored in the resulting encrypted output – it’s an artifact that you’ll have to track separately to get the decrypted values back:

mysql> SET @iv = RANDOM_BYTES(16);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT HEX(AES_ENCRYPT('test', 'key', @iv));
+--------------------------------------+
| HEX(AES_ENCRYPT('test', 'key', @iv)) |
+--------------------------------------+
| 650CE9E699ECA922E09E80CEBE51BFC7     |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT AES_DECRYPT(UNHEX('650CE9E699ECA922E09E80CEBE51BFC7'), 'key', @iv);
+--------------------------------------------------------------------+
| AES_DECRYPT(UNHEX('650CE9E699ECA922E09E80CEBE51BFC7'), 'key', @iv) |
+--------------------------------------------------------------------+
| test                                                               |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)