MySQL Bugs: #8669: AES_DECRYPT fails on null values

Bug #8669	AES_DECRYPT fails on null values
Submitted:	22 Feb 2005 5:56	Modified:	10 Mar 2005 18:16
Reporter:	Ralf Hauser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	mysqld Ver 4.0.21-log for pc-linux-gnu	OS:	Any (*)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
As per http://bugs.mysql.com/bug.php?id=8564, when selecting a list of two values, if I order the list, the values are different than when just retrieved.

What you will see with the below code:
wrong:
  1:testSubj
  2:testSubj
Now second round with correct result - selDraftStmt: 
  1:testSubj
  2:null

How to repeat:
            Statement stmt = null;
            ResultSet rs = null;

            try {
                stmt = this.conn.createStatement(
                        java.sql.ResultSet.TYPE_FORWARD_ONLY,
                        java.sql.ResultSet.CONCUR_UPDATABLE);
                String AESkey = "q2s3d4f5g6o0!;#[";

                stmt.executeUpdate("DROP TABLE IF EXISTS bug8564");
                stmt
                        .executeUpdate("CREATE TABLE bug8564 ("
                                + "msg_id         INT NOT NULL AUTO_INCREMENT UNIQUE, "
                                + "subject        CHAR(255) NOT NULL, PRIMARY KEY (msg_id))");
                stmt.executeUpdate("DROP TABLE IF EXISTS bug8564msg");
                stmt.executeUpdate("CREATE TABLE bug8564msg ("
                        + "msg_id INT NOT NULL AUTO_INCREMENT UNIQUE)");
                stmt
                        .executeUpdate("INSERT INTO bug8564msg (msg_id) VALUES (1);");
                stmt
                        .executeUpdate("INSERT INTO bug8564msg (msg_id) VALUES (2);");

                int autoIncKeyFromApi = -1;

                PreparedStatement insSenderStmt = null;
                insSenderStmt = this.conn.prepareStatement(
                        "INSERT INTO bug8564 (  subject) "
                                + " VALUES (AES_ENCRYPT(?,?));",
                        Statement.RETURN_GENERATED_KEYS);
                insSenderStmt.setString(1, "testSubj"); // subject
                insSenderStmt.setString(2, AESkey);
                log.debug("insSenderStmt: " + //((DelegatingPreparedStatement)
                        insSenderStmt//)
                                //.getDelegate()
                                .toString());
                int retVal = insSenderStmt.executeUpdate();
                rs = insSenderStmt.getGeneratedKeys();
                if (rs.next()) {
                    autoIncKeyFromApi = rs.getInt(1);
                } else {
                    System.out
                            .println("problem with: autoIncKeyFromApi = rs.getInt(1)");
                }
                rs.close();
                System.out.println("Key 1 returned from getGeneratedKeys(): "
                        + autoIncKeyFromApi);
                String aesEncVal = "%EF%BF%AF%13%EF%BF%90%2F%06Wx%03%EF%BE%92%0F%EF%BF%9E%EF%BF%8F%"
                        + "04%EF%BF%A6%EF%BE%82%EF%BE%AEk%17%EF%BF%991%EF%BF%AE%EF%BF%8Cch%EF%BF%B2"
                        + "%EF%BF%97%21%EF%BE%8C%EF%BF%90D%EF%BF%9C%09%3B%EF%BE%90%EF%BF%96n%EF%BE%91"
                        + "%5B%EF%BF%80%0A%3B%EF%BF%BA%EF%BE%BB%3Ad%EF%BF%AB%2F";
                insSenderStmt.setString(1, null);
                insSenderStmt.setString(2, AESkey);
                insSenderStmt = this.conn.prepareStatement(
                        "INSERT INTO bug8564  (subject) VALUES (?);",
                        Statement.RETURN_GENERATED_KEYS);
                insSenderStmt.setString(1, URLDecoder
                        .decode(aesEncVal, "UTF-8"));
                log.debug("insSenderStmt showing bug: "
                        + insSenderStmt.toString());
                retVal = insSenderStmt.executeUpdate();
                rs = insSenderStmt.getGeneratedKeys();
                if (rs.next()) {
                    autoIncKeyFromApi = rs.getInt(1);
                } else {
                    System.out
                            .println("problem with: autoIncKeyFromApi = rs.getInt(1)");
                }
                rs.close();
                System.out.println("Key 2 returned from getGeneratedKeys():"
                        + autoIncKeyFromApi);

                PreparedStatement selDraftStmt = this.conn
                        .prepareStatement("SELECT bug8564.msg_id, AES_DECRYPT(subject,?) AS subject "
                                + " FROM  bug8564 , bug8564msg WHERE "
                                + "bug8564.msg_id = bug8564msg.msg_id "
                                + "ORDER BY msg_id ASC");
                selDraftStmt.setString(1, AESkey);
                System.out.println("selDraftStmt: " + selDraftStmt.toString());
                ResultSet crs = selDraftStmt.executeQuery();
                int i = 0;
                if (crs == null) {
                    log.debug("row set is null!");
                }
                if (!crs.next()) {
                    log.debug("no more results" + i);
                } else {
                    do {
                        i++;
                        System.out.println(crs.getInt("msg_id") + ":"
                                + crs.getString("subject"));
                    } while (crs.next());
                    log.debug(i + " drafts found.");
                }

                selDraftStmt = this.conn
                        .prepareStatement("SELECT bug8564.msg_id, "
                                + " AES_DECRYPT(subject,?) AS subject "
                                + " FROM  bug8564 , bug8564msg WHERE "
                                + "bug8564.msg_id = bug8564msg.msg_id ");
                selDraftStmt.setString(1, AESkey);
                System.out
                        .println("Now second round with correct result - selDraftStmt: "
                                + selDraftStmt.toString());
                crs = selDraftStmt.executeQuery();
                i = 0;
                if (crs == null) {
                    log.debug("row set is null!");
                }
                if (!crs.next()) {
                    log.debug("no more results" + i);
                } else {
                    do {
                        i++;
                        System.out.println(crs.getInt("msg_id") + ":"
                                + crs.getString("subject"));
                    } while (crs.next());
                    log.debug(i + " drafts found.");
                }
                rs.close();

                rs = null;
            } finally {

                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException ex) {
                        // ignore
                    }
                }

                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ex) {
                        // ignore
                    }
                }
            }

Suggested fix:
do not repeat previous value if there is a null value in the string column

confirm  for 4.0.23. 
I tested also against 4.1.10, it works properly with 4.1.10
(attached a testcase that I used)

java source of testcase (according to authors description)

Attachment: bug8669.java (application/octet-stream, text), 6.44 KiB.

This looks like a bug in join processing or optimization. With this test:

create table t1 (id int auto_increment primary key, str char(255) not null);
create table t2 (id int not null unique);
insert into t2 values (1),(2);
insert into t1 (str) values (aes_encrypt('foo', 'bar'));
insert into t1 (str) values ('not valid');
select t1.id, aes_decrypt(str,'bar') from t1, t2 where t1.id = t2.id order by t1.id asc;
drop table t1, t2;

If I remove 'not null' on t1.str, the query returns the expected results.

For the database presented by Jim:

the following query returns a correst result:

mysql> SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id;
+----+-------------------------+
| id | aes_decrypt(str, 'bar') |
+----+-------------------------+
|  1 | foo                     |
|  2 | NULL                    |
+----+-------------------------+

While this modification does not:

mysql> SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id
    ->  ORDER BY t1.id;
+----+-------------------------+
| id | aes_decrypt(str, 'bar') |
+----+-------------------------+
|  1 | foo                     |
|  2 | foo                     |
+----+-------------------------+

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22823

The function AES_DECRYPT can return NULL value even in the case when its first
argument refers to a column declared as NOT NULL.
This fact was not taken into account.

The fix will appear in releases 4.0.24, 4.1.11, 5.0.3.

ChangeSet
  1.2082 05/03/09 00:15:51 igor@rurik.mysql.com +3 -0
  func_str.result, func_str.test:
    Added a test case for bug #8669.
  item_strfunc.cc:
    Fixed bug #8669.
    Function AES_DECRYPT can return NULL value.

Noted in 4.0.24, 4.1.11, 5.0.3 changelogs.

in a variant, it happens again with non-null values

how to reproduce

insert string "1111" with AES key 1
insert string "2222" with AES key 2

select * with AES_DECRYPT with key 1

result received (wrong)
--------------------------
1111
1111

result expected
------------------
�,о�w�/ %�    (i.e. decryption error)
1

forgot to mention - now I am on 4.0.24