Page History

Don't forget to take a full backup before moving on.

Take note of old AES key value for further proceeding.

-- re-encrypt AES values with new key
-- replace <old_key> and <new_key> with actual values
UPDATE SyncopeUser
SET password = encode(encrypt(decrypt(decode(password,'BASE64'),'<old_key>','aes'),'<new_key>','aes'),'BASE64')
WHERE cipherAlgorithm = 'AES' AND password IS NOT NULL;
UPDATE SyncopeUser
SET securityAnswer = encode(encrypt(decrypt(decode(password,'BASE64'),'<old_key>','aes'),'<new_key>','aes'),'BASE64')
WHERE cipherAlgorithm = 'AES' AND securityAnswer IS NOT NULL;

MySQL relies on the block_encryption_mode parameter to handle the AES_ENCRYPT() and AES_DECRYPT() functions.
This means that there is not an easy way to handle the call of both functions in the same SQL statement, when old and new key have different length: because of this, the new AES key must be 16 characters long.
In case this is a limitation, consider taking a different approach, possibly using an external application or relying on a temporary table.

SET block_encryption_mode = 'aes-128-ecb';
SET @old_key = '<old AES key>';
-- due to a MySQL limitation, the length of the new AES key **must** be 16 characters long
SET @new_key = '<new AES key>'; 

-- check if all AES values can be decrypted with the old key; the following query shall return no results
SELECT username FROM SyncopeUser WHERE cipherAlgorithm = 'AES'
AND (password IS NULL OR AES_DECRYPT(FROM_BASE64(password), LEFT(@old_key, 16)) IS NULL)
AND (securityAnswer IS NULL OR AES_DECRYPT(FROM_BASE64(securityAnswer), LEFT(@old_key, 16)) IS NULL);

 -- re-encrypt AES values with new key
UPDATE SyncopeUser
SET password = TO_BASE64(AES_ENCRYPT(CAST(AES_DECRYPT(FROM_BASE64(password), LEFT(@old_key, 16)) AS CHAR(255)), @new_key))
WHERE cipherAlgorithm = 'AES' AND password IS NOT NULL
AND AES_DECRYPT(FROM_BASE64(password), LEFT(@old_key, 16)) IS NOT NULL;
UPDATE SyncopeUser
SET securityAnswer = TO_BASE64(AES_ENCRYPT(CAST(AES_DECRYPT(FROM_BASE64(securityAnswer), LEFT(@old_key, 16)) AS CHAR(255)), @new_key))
WHERE cipherAlgorithm = 'AES' AND securityAnswer IS NOT NULL
AND AES_DECRYPT(FROM_BASE64(securityAnswer), LEFT(@old_key, 16)) IS NOT NULL;

-- check if all AES values can be decrypted with the new key; the following query shall return no results
SELECT username FROM SyncopeUser WHERE cipherAlgorithm = 'AES'
AND (password IS NULL OR AES_DECRYPT(FROM_BASE64(password), @new_key) IS NULL)
AND (securityAnswer IS NULL OR AES_DECRYPT(FROM_BASE64(securityAnswer), @new_key) IS NULL);

SET @old_key = '<old AES key>';
SET @new_key = '<new AES key>'; 

-- check if all AES values can be decrypted with the old key; the following query shall return no results
SELECT username FROM SyncopeUser WHERE cipherAlgorithm = 'AES'
AND (password IS NULL OR AES_DECRYPT(FROM_BASE64(password), LEFT(@old_key, 16)) IS NULL)
AND (securityAnswer IS NULL OR AES_DECRYPT(FROM_BASE64(securityAnswer), LEFT(@old_key, 16)) IS NULL);

-- re-encrypt AES values with new key
-- depending on the actual length, adjust the last AES_ENCRYPT parameter from aes-128-ecb to aes-192-ecb or aes-256-ecb
UPDATE SyncopeUser
SET password = TO_BASE64(AES_ENCRYPT(AES_DECRYPT(FROM_BASE64(password), LEFT(@old_key, 16)), @new_key, NULL, 'aes-128-ecb'))
WHERE cipherAlgorithm = 'AES' AND password IS NOT NULL
AND AES_DECRYPT(FROM_BASE64(password), LEFT(@old_key, 16)) IS NOT NULL;
UPDATE SyncopeUser
SET securityAnswer = TO_BASE64(AES_ENCRYPT(AES_DECRYPT(FROM_BASE64(securityAnswer), LEFT(@old_key, 16)),  @new_key, NULL, 'aes-128-ecb'))
WHERE cipherAlgorithm = 'AES' AND securityAnswer IS NOT NULL
AND AES_DECRYPT(FROM_BASE64(securityAnswer), LEFT(@old_key, 16)) IS NOT NULL;
 
-- check if all AES values can be decrypted with the new key; the following query shall return no results
-- depending on the actual length, adjust the last AES_DECRYPT parameter from aes-128-ecb to aes-192-ecb or aes-256-ecb
SELECT username FROM SyncopeUser WHERE cipherAlgorithm = 'AES'
AND (password IS NULL OR AES_DECRYPT(FROM_BASE64(password), @new_key, NULL, 'aes-128-ecb') IS NULL)
AND (securityAnswer IS NULL OR AES_DECRYPT(FROM_BASE64(securityAnswer), @new_key, NULL, 'aes-128-ecb') IS NULL);

The SQL statements provided below require the SQLPLUS client to be executed.

DEFINE old_key = '1abcdefghilmnopq'
DEFINE new_key = 'NyefOIpekEJVBASb'

-- re-encrypt AES values with new key
-- 4358 is the value for AES 128, 4356 for AES 256
UPDATE SyncopeUser
SET password = UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.base64_encode(DBMS_CRYPTO.encrypt(
DBMS_CRYPTO.decrypt(UTL_ENCODE.base64_decode(UTL_RAW.CAST_TO_RAW(password)), 4358, UTL_RAW.CAST_TO_RAW('&old_key')), 
4358, 
UTL_RAW.CAST_TO_RAW('&new_key'))))
WHERE cipherAlgorithm = 'AES' AND password IS NOT NULL;
UPDATE SyncopeUser
SET securityAnswer = UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.base64_encode(DBMS_CRYPTO.encrypt(
DBMS_CRYPTO.decrypt(UTL_ENCODE.base64_decode(UTL_RAW.CAST_TO_RAW(securityAnswer)), 4358, UTL_RAW.CAST_TO_RAW('&old_key')), 
4358, 
UTL_RAW.CAST_TO_RAW('&new_key'))))
WHERE cipherAlgorithm = 'AES' AND securityAnswer IS NOT NULL;
COMMIT;