Not the answer you need?
Register and ask your own question!

Percona XtraDb cluster AES_Encrypt when Decrypt did not return the character string ?

lokesh123lokesh123 Current User Role Patron
create database encrypted;
use encrypted;
create table t(c1 int, c2 varbinary(255), rnd_pad varbinary(16), primary key(c1));
SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('My secret passphrase',512);
SET @init_vector = RANDOM_BYTES(16);
insert into t (c1,c2, rnd_pad) values (1, AES_ENCRYPT('Secret', @key_str, @init_vector), @init_vector);
-- decrypt data
select c1, AES_DECRYPT(c2,@key_str, rnd_pad) from t;

+----+----------------------------------------------------------------------+
| c1 | AES_DECRYPT(c2,@key_str, rnd_pad)                                    |
+----+----------------------------------------------------------------------+
|  1 | 0x536563726574                                                       |
+----+----------------------------------------------------------------------+

Best Answer

  • lokesh123lokesh123 Current User Role Patron
    edited August 26 Accepted Answer
    I got result when I convert the result in binary to  char.

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    edited August 31
    This appears to be an issue with upstream MySQL.
    I tested the commands you provided on 8.0.16 and they worked correctly. But when I upgraded to 8.0.21 (the latest community version), I get back the same result as you.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 8.0.21 MySQL Community Server - GPL
    ...
    mysql> create database foo;
    Query OK, 1 row affected (0.28 sec)
    mysql> use foo;
    Database changed
    mysql> SET block_encryption_mode = 'aes-256-cbc';
    Query OK, 0 rows affected (0.00 sec)
    mysql> SET @key_str = SHA2('My secret passphrase',512);
    Query OK, 0 rows affected (0.00 sec)
    mysql> SET @init_vector = RANDOM_BYTES(16);
    Query OK, 0 rows affected (0.00 sec)
    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    Query OK, 0 rows affected (0.00 sec)
    mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
    +----------------------------------------------------------------------------------------------+
    | AES_DECRYPT(@crypt_str,@key_str,@init_vector)                                               |
    +----------------------------------------------------------------------------------------------+
    | 0x74657874                                                                                   |
    +----------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    So, this is not an issue specific to PXC, but an issue with Community MySQL. I do not see any bugs on this. I suggest you open a bug report on https://bugs.mysql.com/

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Good that you got a result, but bad that you had to do that extra step which is not documented in the MySQL manual. You could still open a documentation bug on mysql's website for Documentation category.
  • lokesh123lokesh123 Current User Role Patron
    But I am running it on percona Xtradb cluster which handle by percona only?
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    As I said above, this issue is NOT related to PXC. The issue comes from upstream MySQL, produced by Oracle. I am getting the same inconsistent result on MySQL Community 8.0.21.
    Percona is a rebase of Community. This means that we download the Community source code as a "starting point", then we add on to that extra features and enhancements. Thus, if there is a bug within the "starting point", it's a bug in the core Community code, which is included in Percona's code. When MySQL/Oracle Community gets fixed, that fix becomes the new "starting point" for the next version of PXC.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.