Sort by crypted field

Archived from the Xataface Users forum.

inmisia — Mon Sep 28, 2009 12:28 am

Hi Steve,

I need to sort by an encrypted field in the list mode (when click on field name). By default it sorts by encrypted value of field!

I have add this code for decrypt field:

Code: Select all: function cognome__display(&$record) { return $this->gdecrypt($record->Value('cognome')); } function cognome__pushValue(&$record, &$element) { return $this->gcrypt($element->getValue()); } function cognome__pullValue(&$record, &$element) { return $this->gdecrypt($record->Value('cognome')); } function cognome__serialize($value) { if ($_REQUEST['-action']=="edit" || $_REQUEST['-action']=="new") return $value; else return $this->gcrypt($value); }

gcrypt and gdecrypt are my personal methods.

fantomasdm — Mon Sep 28, 2009 9:04 am

From mysql I used AES_ENCRYPT() and AES_DECRYPT() functions for works with encrypted fields.

For example

Code: Select all: select * from anagrafica where AES_DECRYPT(cognome,"key")="rossi"

I using another account but I’m still inmisia!!

inmisia — Wed Sep 30, 2009 1:26 am

I’m using a work round…in file fields.ini I have add this:

Code: Select all: __sql__="select * from anagrafica order by aes_decrypt(cognome,'MySecretKey')"

But don’t like it, becose my secret key is visible and when click on cognome lost order!!

is possible to change __sql__ directive programmatically?

Thanks!

shannah — Wed Sep 30, 2009 11:21 am

Yes. You can change the sql query programmatically.

Here’s what I recommend you do. Graft the decrypted column onto your table, and make this column unreadable (but sortable).

e.g.

Code: Select all: __sql__ = "select m.*, aes_decrypt(my_col, 'key') as mycol2 from mytable m"

Using the __sql__ delegate method it might look like:

Code: Select all: function __sql__(){ return "select m.*, aes_decrypt(my_col, '".AES_KEY."') as mycol2 from mytable m"; }

This allows you to store your key as a constant somewhere else in your app (say in a config file) and use it here.

Make this column inaccessible to all users. You don’t want people seeing the unencrypted data I presume?

Code: Select all: function mycol2__permissions(&$record){ return Dataface_PermissionsTool::NO_ACCESS(); }

Then you can still sort on mycol2 by adding -sort=mycol2 to your query string.

-Steve

inmisia — Thu Oct 01, 2009 3:33 am

Hi Steve, excellent answer, as usualy!!

my Cryptkey is stored in session var in this way:

Code: Select all: if (md5($_POST["psw"])=="111111111111111111111111") { $_SESSION["PWDCRYPT"]=$_POST["psw"]; }

and I using those functions:

Code: Select all: function __sql__() { $sql="select * from anagrafica"; if( isset($_SESSION["PWDCRYPT"]) ) { $sql= "select m.*, aes_decrypt(cognome,'".$_SESSION["PWDCRYPT"]."') decryptCognome from anagrafica m"; } return $sql; } function decryptCognome__permissions(&$record) { return Dataface_PermissionsTool::NO_ACCESS(); }

in index.php
I have add this functions:

Code: Select all: function CryptOrder() { if( !isset($_SESSION["CryptOrder"]) ) { $_SESSION["CryptOrder"]=1; } if ( strpos($_REQUEST['-sort'],'cognome')>=0 and @$_REQUEST['-table'] == 'anagrafica' and isset($_SESSION["PWDCRYPT"]) ) { if($_SESSION["CryptOrder"]==1) { $_REQUEST['-sort'] = $_GET['-sort'] = " decryptCognome asc"; $_SESSION["CryptOrder"]=0; } else { $_REQUEST['-sort'] = $_GET['-sort'] = " decryptCognome desc"; $_SESSION["CryptOrder"]=1; } } }

and call it before xataface block in this way:

Code: Select all: session_start(); CryptOrder(); $dataface="xataface-1.2.1"; require_once '..\\'.$dataface.'\\dataface-public-api.php'; df_init(__FILE__, "http://$_SERVER[HTTP_HOST]/".$dataface); $app =& Dataface_Application::getInstance(); $app->display();

Now It’s working very good!! I hope there isn’t problem for calling session_start() before xataface application!