Which databases support encryption

Question

I use MySQL extensively, but it does not support database-level encryption without any external library.

Which databases have built-in support for the db level, and if you know MySQL enough to compare how close its syntax is to MySQL. I would rather not retrain everything.

I also use PDO extensively to handle SQL injection, which is why PDO-friendly is recommended.

Answer 1

That's right.

SELECT *, AES_DECRYPT(`field`, 'key') as `decrypted` FROM enc WHERE AES_DECRYPT(`field`, 'key')='$input' 

I am not sure if there is a way to only call AES_DECRYPT once in a field.

Answer 2

I know that Oracle 11g supports database level encryption, but I would not say that it is most similar to MySQL ...

Answer 3

I just explored the same problem as mySQL research after using column level and db encryption in SQL Server.

This way, it answered my fears that mySQL did not use db level encryption, but perhaps it is worth mentioning in the answer above, this will cause the table to be scanned every time it is called. This is because each line must be read so that the data can be decrypted before comparison. This invalidates any index that can be placed in the column (if it has any value!) However, the following statement will do the same, but only call ENCRYPT once and possibly bypass any scanned tables. Note. The mySQL syntax is probably incorrect in places.

 DECLARE $EncryptedValue ... SELECT $EncryptedValue = AES_ENCRYPT(`$input`, 'key') SELECT *, $input as `decrypted` FROM enc WHERE `field`=$EncryptedValue 

Answer 4

Adding more to the defendant if someone is interested:

To sort the encrypted AES field,

 ORDER BY LOWER(CONVERT(AES_DECRYPT(field, 'key') USING latin1)) 

Search inside AES encrypted field

 SELECT *, AES_DECRYPT(`field`, 'key') as `decrypted` FROM enc WHERE LOWER(CONVERT(AES_DECRYPT(`field`, 'key') USING latin1)) LIKE '$input' LOWER('%" . $keyword . "%')) 

This is because the AESE_DECRYPT function returns a binary string that cannot be used by LOWER