We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. MVC

Model & db encryption

Hi all,

I want to encrypt a few columns containing personal information is my database (mysql). I want that to be internal to mysql and not in php, so I can still query it if needed (even if decrypting on the fly makes it slower), so I was planning to use AES_ENCRYPT and AES_DECRYPT.

Nothing crazy so far, taht's fairly standard, my question is how to make my models manage that ? I'd like the models to automatically build queries (select + insert + update) managing the encryption for me.

For example: instead of executing:

SELECT myEncryptedColumn FROM myTable;
-->   SELECT AES_DECRYPT(myEncryptedColumn, "myKey")  FROM myTable ;

INSERT INTO myTable (myEncryptedColumn)  VALUES  (value);
-->   INSERT INTO myTable (myEncryptedColumn)  VALUES  (AES_ENCRYPT(value, "myKey"));

Is there anything in phalcon for that, or do I have to overwrite all the models methods myself ? Cheers

2


79.0k

I doubt this can be done via ORM/Models, since that function is rather specific to a certain RDBMS engine, in this case MySQL. IMHO, your best shot would be to extend DB dialect and try to handle it there.



24.8k

Thanks.

I was kinda expecting something like that, your answer totally make sense... Since there are some partials solutions in other frameworks/ORMs, I wanted to ask anyway. I was hoping someone had the same issue in the past, had alreay worked on it, and had some reusable code to share :)

Since I'm planning to encrypt columns from only 1 table, I probably won't go for a generic & extendable solution, I'll just create custom methods & queries in this specif model. (Unless someone shares some code here by then ;) )

Cheers



58.4k

Hello

Why you not use https://docs.phalcon.io/en/latest/reference/crypt.html to encrypt I think it good for solution



24.8k

I don't have anything against Phalcon crypt, or PHP encryption in general, but doing it on the app side creates other difficulties. I'd rather have that fully manage by the db engine if possible.

For instance, since I want to be able to decrypt on the fly while making queries (not talking about password here, it's just encryption-at-rest for some personal data), I would need to choose and maintain an encryption format that mysql support and can decrypt.

That's a scary move because the day I change either PHP or mysql version, the supported encryption formats may change... I know chances are low, but if that ever happens, I can expect a tricky maintenance in which I'll have to update the app encryption mechanism and decrypt and re-encrypt all my records in the db...

Doable, but I'd be happier with less risky solution.



79.0k
edited Jun '16

I agree with you. This task should be handled on RDBMS level.



24.8k

Does anyone know if there's any plan for Phalcon team to bring that feature at some point ?

Because it would be a shame performance & complexity wise to extend in php all the class required to make the encryption transparent so it works with model relations and joined tables... (I guess dialect, querybuilder, pdo/mysql, model maybe ... )