AES_DECRYPT not working on linux : maybe linked to Hibernate

Question

I have encrypted data in my database, and I am trying to execute a request which allows me to display in phpmyadmin the values in clear.

I use the following request :

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

When I use it on the dev environment (windows), it is working well. But once I use it on the pre-prod environment (linux), I get NULL for all values instead.

I am pretty sure it has something to do with the different environments, but I cannot figure out what. I don't even know which function does not act as expected : UNHEX or AES_DECRYPT (my guess would be UNHEX)?

Here are the config of my dev and preprod environments :

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 have continued my researches, and it seams the methods AES_DECRYPT and UNHEX are not guilty. Indeed, if I directly add encrypted value in the table from phpMyAdmin as follows :

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

Then I manage to retrieve the data correctly with the previous SELECT request.

That means the problem must come from the way I insert the data in the first place. For this I use Hibernate and the nullSafeSet method.

What is bothering me is : if there is a problem with the way I save the data, how come it is 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 idea what might cause the problem?

Answer 1

check:

• if you have same data in those databases. connect from your dev to preprod db and check if it works. connect from preprod to your dev database and check if it works. this way you should narrow the problem: db vs environment
• jvm encryption permission. maybe your dev jvm can use strong encryption and preprod can't
• case sensitivity: some DBs (not sure about mysql) store tables in files. windows is case insensitive, linux is not. i've seen problems like that in the past