AES_DECRYPT does not work on Linux: possibly related to Hibernate

Question

I have encrypted data in my database and I am trying to execute a query that allows me to display values ​​in clear in phpmyadmin.

I am using the following query:

SELECT CAST(AES_DECRYPT(`my_encrypted_colum`, UNHEX('pass_in_hexa') AS CHAR) AS clear_value FROM `my_table` 

When I use it in dev (windows) environment, it works well . But as soon as I use it in pre-prod (linux) environment, I get NULL for all values.

I am sure this has something to do with different environments, but I can’t understand that. I don’t even know which function doesn’t work as expected: UNHEX or AES_DECRYPT (I think it will be UNHEX)?

Here is the configuration of my dev and preprod environment:

Dev :

 Serveur : localhost via TCP/IP Type de serveur : MySQL Version du serveur : 5.6.15-log - MySQL Community Server (GPL) Version du protocole : 10 Utilisateur : root@localhost Jeu de caractères du serveur : UTF-8 Unicode (utf8) Apache/2.2.25 (Win32) PHP/5.3.19 Version du client de base de données : libmysql - mysqlnd 5.0.8-dev - 20102224 - $Id: 65fe78e70ce53d27a6cd578597722950e490b0d0 $ Extension PHP : mysqli 

Preprod :

 Serveur: Localhost via UNIX socket Logiciel: MySQL Version du logiciel: 5.6.14 - MySQL Community Server (GPL) Version du protocole: 10 Utilisateur: root@localhost Jeu de caractères du serveur: UTF-8 Unicode (utf8) Apache/2.2.15 (CentOS) Version du client de base de données: libmysql - 5.1.72 Extension PHP: mysqli 

EDIT :

I continued my research, and he throws the AES_DECRYPT and UNHEX methods not to blame . Indeed, if I directly add the encrypted value to the table from phpMyAdmin as follows:

  INSERT INTO `my_table` (`my_encrypted_column`) VALUES (AES_ENCRYPT('blabla', UNHEX('pass_in_hexa')) 

Then I will be able to recover the data correctly with the previous SELECT query.

This means that the problem should come from how I insert the data first. For this, I use Hibernate and the nullSafeSet method.

What bothers me: if there is a problem with how I save data, how it works , working on Windows, but not on Linux ?

Below are my implementations of nullSafeSet and nullSafeGet

 private static final String CIPHER_ALGORITHM = "AES"; // nullSafeSet protected void noNullSet(PreparedStatement st, Object value, int index, SessionImplementor si) throws SQLException { byte[] clearText = ((String) value).getBytes(Charset.forName("UTF-8")); try { Cipher encryptCipher = Cipher.getInstance(CIPHER_ALGORITHM); encryptCipher.init(Cipher.ENCRYPT_MODE, getKey(cle)); st.setBytes(index, encryptCipher.doFinal(clearText)); } catch (GeneralSecurityException e) { throw new RuntimeException("should never happen", e); } } @Override public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor si, Object owner) throws HibernateException, SQLException { byte[] bytes = rs.getBytes(names[0]); try { Cipher decryptCipher = Cipher.getInstance(CIPHER_ALGORITHM); decryptCipher.init(Cipher.DECRYPT_MODE, getKey(cle)); if (bytes != null) { return new String(decryptCipher.doFinal(bytes), Charset.forName("UTF-8")); } else { return new String(); } } catch (GeneralSecurityException e) { throw new RuntimeException("Mauvaise clé"); } } private static SecretKeySpec getKey(String secretKey) { final byte[] finalKey = new byte[16]; int i = 0; for (byte b : secretKey.getBytes()) { // XOR finalKey[i++ % 16] ^= b; } return new SecretKeySpec(finalKey, "AES"); } 

Do you have any ideas what might cause the problem?

Answer 1

So, I was finally able to indicate where the problem was.

When starting the application, I saved the key in the Configuration object. The key was saved as a string, making String cle = new String(key.getEncoded());

This works fine on windows and returns me the following key:

 *£Ðtôµ•Ã 

But on linux, special characters were not selected correctly.

 *  t     

This led to the fact that encryption / decryption was performed using the wrong key in linux, so I could not use the "correct" key value when doing "SELECT" in MySQL

Answer 2

I think you might have a chain / padding difference between your systems due to the fact that you are not installing them explicitly. Try

 private static final String CIPHER_ALGORITHM = "AES/ECB/PKCS5Padding"; 

to get an instance of Cipher, as this should be implemented in MySql, as suggested here .

If you have a passphrase longer than 8 characters (bytes) in length, mysql uses its own implementation of generating a 128-bit AES key from it, as suggested here . It may be different from the key Java used when your passphrase is longer than 8 bytes.

Answer 3

check:

• if you have the same data in these databases. connect from your developer to preprod db and check if it works. connect from preprod to the dev database and check if it works. so you have to narrow down the problem: db vs environment
• jvm encryption resolution. maybe your dev jvm can use strong encryption, and preprod cannot
• case sensitivity: some DBs (not sure about mysql) store tables in files. windows are not case sensitive, linux is not. I have seen such problems in the past

Answer 4

You may have a character set problem. If one of your MySQL databases works with the latin1 character set, inserting UTF-8 data will scramble the data.

To check the character sets in the corresponding table, run this command on both systems:

 mysql> show create table foo | foo | CREATE TABLE `foo` ( `FLD1` int(11) DEFAULT NULL, `FLD2` int(11) DEFAULT NULL, `FLD3` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 

It will show you which character set is used in this particular table. If it is not UTF8, then it will not match your code, and you will have problems with data corruption.

If this is really a problem, you can change the character set by running the ALTER statement as follows:

 ALTER TABLE foo CONVERT TO CHARACTER SET utf8