3 replies to this topic

[Vaielab]

Hello,

I didn't know if I should put this question in the php section or in the database section, but since I beleive the answer will be in php, I guess I will post it here.

So as the title say, I want to encrypt the data in my database.
I use mysql, and to communicate with it, I use PHP + PDO.
And I always prepare my function, and use execute(array()) to call PDO like this

$sth = $dbh->prepare('select ... where id=:id');

$sth->execute(array(':id' => $id))

My first though was to override the execute and fetch function of PDO, to pass them throw a encryption function, but at the moment of execution, I do not know what columns has to be encrypted (such as varchar, text), and witch can't be encrypted (such as id, or date)

So my next tough was to rewrite some of my code, and insted of using execute(array), use bindParam or bindValue
It would be a pain to rewrite my code, but would work... except when I fetch, I would not know when the data is encrypted, and when it isn't

Anyone have an idea for this?

[WingedPanther]

I have a BIG concern about this. Encryption can potentially change the size of the field. So if you have a Varchar(50), and you have a 50 character string, the encrypted string could be 75 characters.

I think a better question would be: what data NEEDS to be encrypted?

[Vaielab]

I read somewhere that you can predict the "grow size" (sorry can't find of a better way to say augmentation of the size) depending of the type of encoding you do.
So if I have a varchar(100) (example), and my encoding will do a maximum grow size of 20%, I can change my data to 120 without a problem

[Alexander]

Vaielab said:

I read somewhere that you can predict the "grow size"

The term mainly used in such algorithms is 'expansion'. Each block/bit is expanded, and a larger text size is a byproduct of this.

It can be predicted (see below) due to padding.

Quote

I do not know what columns has to be encrypted (such as varchar, text), and witch can't be encrypted (such as id, or date)

You should consider rebuilding the queries to encrypt, where encrypted columns are required.

Juggling extended classes may take a long time, especially when deciding how to encrypt only certain things, and taking a lot of time on PHP's side for the encryption.

INSERT INTO `FOO` (id, address) VALUES (:id, AES_ENCRYPT(:address, :key))

...

SELECT AES_DECODE(name, :key) FROM `FOO` WHERE name = AES_ENCODE(:username, :key)

You cannot really rely on AES_DECRYPT failing on non-encrypted information however, as it may return garbage. You may have to encrypt all old data, and also find out what your key scheme will be.

---------------------

Expansion for AES 128 (16*8=128):

Expandedbytes = 16 * (strlen(string)/ 16) + 1)

string length is in bytes, not characters (as a multibyte character may increase the resulting size)

Alexander.