Encrypting content stored in DB

A place for users and developers of the Xataface to discuss and receive support.

Encrypting content stored in DB

Postby Rrrr7 » Wed Dec 19, 2012 7:00 am

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 ?
Rrrr7
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am

Re: Encrypting content stored in DB

Postby shannah » Wed Dec 19, 2012 9:34 am

You can implement fieldname__serialize() and fieldname__unserialize() methods in your delegate class where you encrypt and decrypt your data.
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Encrypting content stored in DB

Postby Rrrr7 » Wed Dec 19, 2012 9:44 am

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


You lost me there :lol:

Can you give me a link to an example or documentation ? Thank You !
Rrrr7
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am

Re: Encrypting content stored in DB

Postby shannah » Wed Dec 19, 2012 10:47 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Encrypting content stored in DB

Postby Rrrr7 » Sat Jan 05, 2013 6:10 pm

shannah wrote: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 ?
Rrrr7
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am

Re: Encrypting content stored in DB

Postby shannah » Sun Jan 06, 2013 10:42 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Encrypting content stored in DB

Postby Rrrr7 » Mon Jan 07, 2013 2:41 pm

shannah wrote: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.
Rrrr7
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am

Re: Encrypting content stored in DB

Postby shannah » Mon Jan 07, 2013 3:41 pm

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Encrypting content stored in DB

Postby Rrrr7 » Mon Jan 07, 2013 4:20 pm

shannah wrote: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.
Rrrr7
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am

Re: Encrypting content stored in DB

Postby shannah » Tue Jan 08, 2013 9:25 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Encrypting content stored in DB

Postby Rrrr7 » Tue Jan 08, 2013 12:14 pm

shannah wrote:
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
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am

Re: Encrypting content stored in DB

Postby Rrrr7 » Sun Jan 13, 2013 11:46 am

I succeeded using the grafted field method !

Thank You !
Rrrr7
 
Posts: 14
Joined: Fri Dec 14, 2012 7:37 am


Return to Xataface Users

Who is online

Users browsing this forum: Google [Bot] and 2 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved