[MDEV-15619] using CONVERT() inside AES_ENCRYPT() in an UPDATE corrupts data Created: 2018-03-21  Updated: 2020-08-25  Resolved: 2018-03-26

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.0, 10.1, 10.0.32, 10.1.31, 10.2.13, 10.2, 10.3
Fix Version/s: 10.0.35, 10.1.33, 10.2.15, 10.3.6

Type: Bug Priority: Critical
Reporter: Rick Pizzi Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: None


 Description   

Customer trying to do the following:

INSERT INTO `t1` VALUES (1,'',NULL,38391,'2017-06-24 07:35:28');
 
update t1 set directory = AES_ENCRYPT(CONVERT('test stringrererejrjerjehrjekhrjkehrjkehrkjehrjkerhkjehrjekrhkjehrkjerhjkehrkjehrkjehrjkehrjkehrjkehrjkerjkehrjkehrjkehrjke rekjhrejrejhrjehgrehjgrhjerjhegrjherejhgrjhegrjehgrjhegrejhrgjehgrjhegrjhegrjhergjhegrjhegrhjegrjerhthkjjkdhjkgdfjkgjkdgdjkfjkhgjkfdhjgjkfdghkjdfghkjfdghfjkdghkdjfghdkjfghfjkdghfkjdghkjfdghfkjdghfkdjghfkjdghfdjkghjkdfhgdfjkghfjkdghfjkdghfjdkghfjkdghkfjdghfkjdghfkjdghkjdfghfjdkghjkfdghkjdfhgjkdfhgjkfdhgkjfdghkfjdhgkjfdgdjkejktjherjthkjrethkjrethjkerthjkerhtjkerhtkjerhtjkerhtjkerhtjkrehtkjerhtkjrehtjkrehtkjrehtkjerhtkjerhtjkrehtkjrehtjkrehtkjrethjkrethkjrehtkjethjkerhtjkrehtjkretkjerhtkjrehtjkerhtjkrehtjrehtkjrekjtrfgdsfgdhjsghjgfdhjsfhjdfgdhjshjdshjfghjdsfgjhsfgjhsdfgjhdsfgjdhsfgsjhfgjhsdfgsdjhfgjdhsfdjshfgdsjhfgjsdhfdjshfgdjhsfgdjshfgjdhsfgjhsdfgjhsdgfjhsdgfjhdsgfjhsgfjhsdgfjhdsgfhjsdehkjthrkjethjkre' USING latin1), '95F5A1F52A554'), last_update= NOW();

Table definition:

Create Table: CREATE TABLE `t1` (
 
  `id` int(11) NOT NULL,
 
  `session_id` varchar(255) DEFAULT NULL,
 
  `directory` mediumtext,
 
  `checksum` int(10) DEFAULT NULL,
 
  `last_update` datetime DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `lastupdate` (`last_update`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The above update saves corrupted data.

  • On 10.0 it saves NULL
  • On 10.1 and 10.2 it saves an encrypted version of the empty string
  • This only happens when UPDATING; running SELECT AES_ENCRYPT.(CONVERT(...)) works fine!
  • It appears to work fine up to some length of the string to be converted (issue does not happen if the string fed to CONVERT() is less than 700 chars or so)

 Comments   
Comment by Steven McDowall [ 2018-03-21 ]

BTW – it USED to work in prior versions of the main versions:
10.0.12 OK
10.0.33 NOT OK

10.1.16 OK
10.1.31 NOT OK

10.3.0 OK

Comment by Steven McDowall [ 2018-03-23 ]

Are we sure it's AES_ENCRYPT that broke or CONVERT ?? I forgot But either way, figure out what broke and in what version (and not just 10.1 I'd like 10.0 too since we run a lot of that!)

Comment by Rick Pizzi [ 2018-03-23 ]

It's the combo of both it seems, and only when updating a row.

Comment by Rick Pizzi [ 2018-03-23 ]

This problem appeared first in 10.0.32 and 10.1.25

Comment by Steven McDowall [ 2018-03-23 ]

MariaDB Engineers – can you find the common commit (one presumes) in those versions and come back with what the bug is – what got introduced, etc? Thanks

Comment by Elena Stepanova [ 2018-03-23 ]

The problem was introduced by this commit:

commit 3a37afec293e36e51b83a9bd338ad5f74e7f63c0
 
Author: Alexander Barkov <bar@mariadb.org>
 
Date:   Mon Jun 19 12:45:32 2017 +0400
 
 
 
    MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery

Comment by Alexander Barkov [ 2018-03-26 ]

A smaller test case reproducing the problem:

CREATE OR REPLACE TABLE t1 (
 
  id int(11) NOT NULL PRIMARY KEY,
 
  directory mediumtext
 
) DEFAULT CHARSET=latin1;
 
INSERT INTO t1 VALUES (1,AES_ENCRYPT(CONVERT(REPEAT('a',800) USING latin1),'95F5A1F52A554'));
 
SELECT * FROM t1;


+----+-----------+
 
| id | directory |
 
+----+-----------+
 
|  1 | NULL      |
 
+----+-----------+

If I change 800 to 700, it inserts non-NULL data.

Comment by Alexander Barkov [ 2018-03-26 ]

Starting from 10.1, the problem is also repeatable with AES_DECRYPT():

SET @enc=AES_ENCRYPT(REPEAT(_latin1'a',800),'95F5A1F52A554');
 
CREATE OR REPLACE TABLE t1 (
 
  id int(11) NOT NULL PRIMARY KEY,
 
  directory mediumtext
 
) DEFAULT CHARSET=latin1;
 
INSERT INTO t1 VALUES (1,AES_DECRYPT(CONVERT(@enc USING binary),'95F5A1F52A554'));
 
SELECT * FROM t1;

Comment by Alexander Barkov [ 2018-03-26 ]

The problem is that Item_func_aes_encrypt::val_str() and Item_func_aes_decrypt::val_str() use str_value as a buffer internally. This is wrong. There should be a separate buffer to get args[0]->val_str().

Generated at Thu Feb 08 08:22:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.