Encrypting content stored in DB

[Rrrr7]

Hi,
I use aes encryption for user passwords and I wonder if there is a way to encrypt other fields from other tables like "zip code" for example.

Is this possible ?

[shannah]

You can implement fieldname__serialize() and fieldname__unserialize() methods in your delegate class where you encrypt and decrypt your data.

[Rrrr7]

You can implement fieldname__serialize() and fieldname__unserialize() methods in your delegate class where you encrypt and decrypt your data.

You lost me there

Can you give me a link to an example or documentation ? Thank You !

[shannah]

fieldname__serialize() is called before saving data to the database to "serialize" it. You can perform encryption in this step.
fieldname__unserialize() is called just after loading data from the database to "unserialize" it. You can perform decryption in this step.

I don't have any examples of unserialize of the top of my head, but you can check out
http://xataface.com/wiki/Authenticating ... sers_table
and
http://xataface.com/wiki/Authenticating ... sers_Table
for examples of using serialize to encrypt columns with custom encryption algorithms.

Note that both of these examples use one-way encryption algorithms. If you want to be able to decrypt, you would need to use a 2-way algorithm.

-Steve

[Rrrr7]

fieldname__serialize() is called before saving data to the database to "serialize" it. You can perform encryption in this step.
fieldname__unserialize() is called just after loading data from the database to "unserialize" it. You can perform decryption in this step.

I don't have any examples of unserialize of the top of my head, but you can check out
http://xataface.com/wiki/Authenticating ... sers_table
and
http://xataface.com/wiki/Authenticating ... sers_Table
for examples of using serialize to encrypt columns with custom encryption algorithms.

Note that both of these examples use one-way encryption algorithms. If you want to be able to decrypt, you would need to use a 2-way algorithm.

-Steve

Can we use the AES_ENCRYPT and AES_DECRYPT functions that mySql has ?

[shannah]

You could use these functions, although you would have to use a bit of a workaround since you can't override a particular column with a function like this. You would need to do this in two parts.

1. Set up the field to save using aes_encrypt by setting the following on the field definition in the fields.ini file:

Code: Select all

[myfield]
encryption=aes_encrypt
aes_key="the secret aes key"

2. This field will be saved encrypted, but it will also be loaded encrypted, so if you want it to be loaded decrypted, you'll need create a grafted field using the __sql__ directive in the fields.ini file that decrypts the field.
e.g.
[code]
__sql__ = "select t.*, aes_decrypt(t.myfield, 'the secret aes key') as myfield_decrypted from mytable t"


Or something along these lines.

-Steve

[Rrrr7]

You could use these functions, although you would have to use a bit of a workaround since you can't override a particular column with a function like this. You would need to do this in two parts.

1. Set up the field to save using aes_encrypt by setting the following on the field definition in the fields.ini file:

Code: Select all

[myfield]
encryption=aes_encrypt
aes_key="the secret aes key"

2. This field will be saved encrypted, but it will also be loaded encrypted, so if you want it to be loaded decrypted, you'll need create a grafted field using the __sql__ directive in the fields.ini file that decrypts the field.
e.g.
[code]
__sql__ = "select t.*, aes_decrypt(t.myfield, 'the secret aes key') as myfield_decrypted from mytable t"


Or something along these lines.

-Steve

Perfect, it works.

Now if I can somehow make a button or a link to an action that actually shows the decrypted value only when run. It currently creates a new column that lists all values decrypted. I would like to use this for a list of passwords stored in a database.

[shannah]

I see. It is probably best not to use the approach I described then. If I were you, I would create a custom action that just retrieves the decrypted password. Set the permissions to that users can only access this action to retrieve their own password. Then just use a mysql_query() with your aes_decrypt() call to retrieve this value manually.

Once you have an action that does this, you can add an AJAX call anywhere in the interface to obtain this password.

Or if you don't want to use AJAX, just create a normal HTML action that includes the decrypted password in its output.

-Steve

[Rrrr7]

I see. It is probably best not to use the approach I described then. If I were you, I would create a custom action that just retrieves the decrypted password. Set the permissions to that users can only access this action to retrieve their own password. Then just use a mysql_query() with your aes_decrypt() call to retrieve this value manually.

Once you have an action that does this, you can add an AJAX call anywhere in the interface to obtain this password.

Or if you don't want to use AJAX, just create a normal HTML action that includes the decrypted password in its output.

-Steve

That would be an ideea....

I'm still thinking how to do this. I have these tables that contain information about websites, the links to the administration panels and username and passwords. My goal is to encrypt the passwords stored in the database and also display the decrypted passwords only when clicked, like within a additional pop-up page or some other form just not in pure plain sight as a list.

[shannah]

I'm still thinking how to do this.

Is that a question about how to create custom actions in Xataface to achieve this, or are you just mulling over your options out loud?

-Steve

[Rrrr7]

I'm still thinking how to do this.

Is that a question about how to create custom actions in Xataface to achieve this, or are you just mulling over your options out loud?

-Steve

I was thinking loudly, yes !

[Rrrr7]

I succeeded using the grafted field method !

Thank You !