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:
select encode(encrypt(cast('0123456789101112' as bytea),cast('passwordshouldbe' as bytea),'aes'),'BASE64');(length=15) produces
pqWvs6RxsAqPRVUK7VFy5w==
and
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 is same in both cases but not for the ones above 15.
My problem is that I cant use the query I wrote for joining the tables, since trying to decrypt the values generated by java code, with encrypt in pgcrypto gives me error as
SQL Error [22021]: ERROR: invalid byte sequence for encoding "UTF8": 0x90
I can decrypt the Java code generated encryted string with other platforms as well
eg https://www.devglan.com/online-tools/aes-encryption-decryption here
but I can only decrypt the 'pgcrypto encrypt' generated encrypted string(for data > 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;
}
}