[MDEV-15619] using CONVERT() inside AES_ENCRYPT() in an UPDATE corrupts data - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.32, 10.1.31, 10.2.13, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.0.35, 10.1.33, 10.2.15, 10.3.6
Component/s: Data Manipulation - Update
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)

Attachments

Activity

Ascending order - Click to sort in descending order

Rick Pizzi (Inactive) created issue - 2018-03-21 15:41

Steven McDowall added a comment - 2018-03-21 18:16

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

Steven McDowall added a comment - 2018-03-21 18:16 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

Steven McDowall added a comment - 2018-03-23 13:13

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

Steven McDowall added a comment - 2018-03-23 13:13 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!)

Rick Pizzi (Inactive) added a comment - 2018-03-23 14:35

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

Rick Pizzi (Inactive) added a comment - 2018-03-23 14:35 It's the combo of both it seems, and only when updating a row.

Rick Pizzi (Inactive) added a comment - 2018-03-23 14:39

This problem appeared first in 10.0.32 and 10.1.25

Rick Pizzi (Inactive) added a comment - 2018-03-23 14:39 This problem appeared first in 10.0.32 and 10.1.25

Steven McDowall added a comment - 2018-03-23 14:52

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

Steven McDowall added a comment - 2018-03-23 14:52 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

Rick Pizzi (Inactive) made changes - 2018-03-23 15:21

Field	Original Value	New Value
Description	Customer trying to do the following: {code} update t1 set directory = AES_ENCRYPT(CONVERT('test stringrererejrjerjehrjekhrjkehrjkehrkjehrjkerhkjehrjekrhkjehrkjerhjkehrkjehrkjehrjkehrjkehrjkehrjkerjkehrjkehrjkehrjke rekjhrejrejhrjehgrehjgrhjerjhegrjherejhgrjhegrjehgrjhegrejhrgjehgrjhegrjhegrjhergjhegrjhegrhjegrjerhthkjjkdhjkgdfjkgjkdgdjkfjkhgjkfdhjgjkfdghkjdfghkjfdghfjkdghkdjfghdkjfghfjkdghfkjdghkjfdghfkjdghfkdjghfkjdghfdjkghjkdfhgdfjkghfjkdghfjkdghfjdkghfjkdghkfjdghfkjdghfkjdghkjdfghfjdkghjkfdghkjdfhgjkdfhgjkfdhgkjfdghkfjdhgkjfdgdjkejktjherjthkjrethkjrethjkerthjkerhtjkerhtkjerhtjkerhtjkerhtjkrehtkjerhtkjrehtjkrehtkjrehtkjerhtkjerhtjkrehtkjrehtjkrehtkjrethjkrethkjrehtkjethjkerhtjkrehtjkretkjerhtkjrehtjkerhtjkrehtjrehtkjrekjtrfgdsfgdhjsghjgfdhjsfhjdfgdhjshjdshjfghjdsfgjhsfgjhsdfgjhdsfgjdhsfgsjhfgjhsdfgsdjhfgjdhsfdjshfgdsjhfgjsdhfdjshfgdjhsfgdjshfgjdhsfgjhsdfgjhsdgfjhsdgfjhdsgfjhsgfjhsdgfjhdsgfhjsdehkjthrkjethjkre' USING latin1), '95F5A1F52A554'), last_update= NOW(); {code} Table definition: {code} 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 {code} 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)	Customer trying to do the following: {code} 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(); {code} Table definition: {code} 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 {code} 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)

Elena Stepanova made changes - 2018-03-23 15:54

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2018-03-23 15:55

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

Elena Stepanova added a comment - 2018-03-23 15:55 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

Elena Stepanova made changes - 2018-03-23 15:58

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Affects Version/s		10.0.32 [ 22504 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s	10.0.33 [ 22552 ]
Assignee		Alexander Barkov [ bar ]

Geoff Montee (Inactive) made changes - 2018-03-23 16:27

Priority

Major [ 3 ]

Critical [ 2 ]

Alexander Barkov added a comment - 2018-03-26 04:02 - edited

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.

Alexander Barkov added a comment - 2018-03-26 04:02 - edited 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.

Alexander Barkov made changes - 2018-03-26 04:26

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Alexander Barkov added a comment - 2018-03-26 05:29

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;

Alexander Barkov added a comment - 2018-03-26 05:29 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;

Alexander Barkov added a comment - 2018-03-26 05:34

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

Alexander Barkov added a comment - 2018-03-26 05:34 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() .

Alexander Barkov made changes - 2018-03-26 06:34

issue.field.resolutiondate

2018-03-26 06:34:41.0

2018-03-26 06:34:41.04

Alexander Barkov made changes - 2018-03-26 06:34

Fix Version/s		10.0.35 [ 22912 ]
Fix Version/s		10.1.32 [ 22908 ]
Fix Version/s		10.2.14 [ 22911 ]
Fix Version/s		10.3.6 [ 23003 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2018-03-26 10:11

Fix Version/s		10.1.33 [ 22909 ]
Fix Version/s		10.2.15 [ 23006 ]
Fix Version/s	10.1.32 [ 22908 ]
Fix Version/s	10.2.14 [ 22911 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 86128 ]

MariaDB v4 [ 153994 ]

Jira Automation (IT) made changes - 2024-07-04 07:54

Zendesk Related Tickets

181911

People

Assignee:: Alexander Barkov

Reporter:: Rick Pizzi (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2018-03-21 15:41

Updated:: 2024-07-08 00:22

Resolved:: 2018-03-26 06:34

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server