Bug #35087 Inserting duplicate values at one time with DES_ENCRYPT leads to wrong results
Submitted: 5 Mar 2008 17:54 Modified: 12 May 2009 2:30
Reporter: Chris Calender Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.50+ OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: DES_DECRYPT, DES_ENCRYPT, duplicate, Inserting duplicate values at one time
Triage: Triaged: D2 (Serious)

[5 Mar 2008 17:54] Chris Calender
Description:
If you insert duplicate values with DES_ENCRYPT in a single query, the data is not inserted properly.  Some extra characters get added to each successive entry, in this case, when they should not.  You can insert duplicate values one at a time, and it works as expected.

How to repeat:
First, you must have an SSL enabled version of MySQL.

Second, you must have your SSL certificates set up properly for the DES_ENCRYPT/DES_DECRYPT functions to work as expected.

CREATE TABLE IF NOT EXISTS t1 (des VARBINARY(200) NOT NULL DEFAULT '') ENGINE=MyISAM;

INSERT INTO t1 VALUES ('1234'), ('12345'), ('123456'), ('1234567');

UPDATE t1 SET des=DES_ENCRYPT('1234');

SELECT DES_DECRYPT(des) FROM t1;

You'll see the extra characters that are being inserted.

+--------------------------+
| DES_DECRYPT(des)         |
+--------------------------+
| 1234                     |
| 1234***♦                 |
| 1234***♦******           |
| 1234***♦************     |
+--------------------------+

Occurs with varying column types (such as varbinary, blob, tinyblob, varchar).

Note that it can work correctly if you do not surround the updated text with quotes (''), however, that will only work if inserting INTs.  For example, UPDATE t1 SET des=DES_ENCRYPT(1234); will work as expected.  However, this UPDATE will fail if you try to insert text rather than an INT.

Suggested fix:
It seems like there may be a bug in the re-init of DES functions.
[13 Apr 2009 13:48] Bugs System
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/commits/71928
[16 Apr 2009 13:54] Bugs System
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/commits/72303
[17 Apr 2009 15:56] Bugs System
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/commits/72415
[17 Apr 2009 15:58] Bugs System
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/commits/72416
[5 May 2009 18:52] Bugs System
Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)
[5 May 2009 19:39] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:11] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:chad@mysql.com-20090417203645-a3bt7h2l3mtvsw27) (merge vers: 6.0.11-alpha) (pib:6)
[12 May 2009 2:30] Paul Dubois
Noted in 5.0.82, 5.1.35, 6.0.12 changelogs.

An UPDATE statement that updated a column using the same DES_ENCYPT()
value for each row actually updated different rows with different
values.
[15 Jun 2009 8:26] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:05] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:46] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)