Postgres AES encryption with pgcrypto encrypt for text of length >15 characters is not compatible with other platforms
1
vote
0
answers
1586
views
I am in a need of joining 2 tables with stringed identifiers, the key in first table is encrypted with AES ECB and the one in the second table is a raw form of that field in first table.
I am trying to achieve this by doing something like
CREATE EXTENSION pgcrypto;
select * from table1
inner join table2 on
( table2.rawid = convert_from(decrypt(decode(table2.encid,'BASE64'),'passwordshouldbe','AES'), 'UTF-8'));
This should work and it does, until the encrypted text of length > 15. Take for example:
1. select encode(encrypt(cast('0123456789101112' as bytea),cast('passwordshouldbe' as bytea),'aes'),'BASE64');
(length=15) produces pqWvs6RxsAqPRVUK7VFy5w==
and
2. select encode(encrypt(cast('0123456789101112' as bytea),cast('passwordshouldbe' as bytea),'aes'),'BASE64');
(length = 16) produces +p3iTMN7zmb0wh1lk2Wk+Hbfj6WbqP1ECgtPci4nbW8=
My java code produces the encryption as pqWvs6RxsAqPRVUK7VFy5w==
and +p3iTMN7zmb0wh1lk2Wk+I64/ZdIsIaXiPkdDpkCzgY=
respectively for each of the cases.
The encrypted form of the string with `length 15 in length) from the postgresql only.
Can anyone guide me somewhere here regarding what pgcrypto is doing for encrypting longer texts than length 15.
If it is of any help, I am including the java code I used for encryption of the data.
public class Utilities {
private static final String ALGO = "AES";
private static final byte[] keyValue = new byte[]{0x70, 0x61, 0x73, 0x73, 0x77, 0x6f, 0x72, 0x64, 0x73, 0x68, 0x6f, 0x75, 0x6c, 0x64, 0x62, 0x65};
//passwordshouldbe
public static String encrypt(String data) {
try {
Key key = generateKey();
Cipher c = Cipher.getInstance(ALGO);
c.init(Cipher.ENCRYPT_MODE, key);
byte[] encVal = c.doFinal(data.getBytes());
return Base64.getEncoder().encodeToString(encVal);
} catch (Exception ex) {
logger.error(ex.toString());
return null;
}
}
public static String decrypt(String encryptedData) {
try {
Key key = generateKey();
Cipher c = Cipher.getInstance(ALGO);
c.init(Cipher.DECRYPT_MODE, key);
byte[] decordedValue = Base64.getDecoder().decode(encryptedData);
byte[] decValue = c.doFinal(decordedValue);
return new String(decValue);
} catch (Exception ex) {
logger.error(ex.toString());
return null;
}
}
private static Key generateKey() {
Key key = new SecretKeySpec(keyValue, ALGO);
return key;
}
}
Asked by desmostachya
(111 rep)
Jan 12, 2023, 04:35 PM
Last activity: Jan 12, 2023, 08:35 PM
Last activity: Jan 12, 2023, 08:35 PM