1

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

  1. 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; }

}

Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25
desmostachya
  • 111
  • 3

0 Answers0