Which databases support encryption

I use MySQL extensively, but it doesn't support database-lev开发者_如何学Goel encryption without some external library.

What databases come with db-level support built-in, and if you know MySQL enough to compare, how close is its syntax to MySQL. I'd obviously prefer not to re-learn everything.

I also use PDO extensively to handle sql injections, so ideally PDO-friendly is preferred.

Absolutely.

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

I am not sure if there's a way of only calling AES_DECRYPT once per field.

I know Oracle 11g supports database level encryption, but i wouldn't say it's the most similar to MySQL...

I've just been researching the same problem as I'm investigating mySQL after having used both column and db level encryption in SQL Server.

So this has answered my concerns that mySQL did not have out of the box db level encryption but it may be worth mentioning in the answer given above it would result in a table scan every time it is called. This is because each row will have to be read so the data can be decrypted before being compared. That invalidates any index that may have been put on the column (if there's any value in that too!) However the following statement would do the same thing but only calls ENCRYPT once and possibly gets around any table scans. Note: mySQL syntax is probably incorrect in places.

DECLARE $EncryptedValue ...
SELECT $EncryptedValue = AES_ENCRYPT(`$input`, 'key') 

SELECT 
   *, 
  $input as `decrypted` 
FROM enc 
WHERE 
  `field`=$EncryptedValue

Adding more to the answerer if someone is interested :

To sort an AES encrypted field,

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

To search inside an 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 done because the AESE_DECRYPT function returns a binary string which cannot be used by LOWER