I have this MySQL query using
AES_DECRYPT() that is the following:
SELECT * FROM data_tbl WHERE AES_DECRYPT(data_point,'aBcDeF')='data_1';
I've figured out as much that the equivalent in Postgres 11 is
decrypt() (F.25.4. Raw Encryption Functions) and the query would look something like this:
SELECT * FROM data_tbl AND decrypt(data_point,'aBcDeF','aes')='data_1';
At least, the documentation for both leads me to believe that these are equivalent.
The data was dumped from MySQL and imported into PostgreSQL.
data-pointfield in MySQL is type
varbinary(255)and the values all show
BLOBin MySQL Workbench.
data_pointfield in Postgres is type
byteaand the values all show
[binary data]in pgAdmin.
Whether those data types are equivalent, I admit, I'm not sure.
The MySQL query works and finds a record based on he criteria. The PostgreSQL does not, although the record is in there. So it seems like one of three things:
- The data types (
bytea) are not equivalent
- The data does not match (
[binary data]) are not equivalent
- The functions (
decrypt()) are not equivalent
I did try to decrypt the value in PostgreSQL just to see what it is in a readable format by first trying:
SELECT decrypt(data_point, 'aBcDeF', 'aes') FROM data_tbl;
But that just returned
[binary data] for all the rows.
Then I came across this answer to use
convert_from as in:
SELECT convert_from(decrypt(data_point, 'aBcDeF', 'aes'), 'SQL_ASCII') FROM data_tbl;
However, I just get this error:
ERROR: invalid byte sequence for encoding "UTF8": 0xcf 0xf5 SQL state: 22021
So at this point I'm stumped as to how to resolve the primary issue. The primary issue being doing the equivalent of
AES_DECRYPT but in PostgreSQL 11. The last error message isn't so much an issue, but it is likely related to
decrypt() not returning the same results.