Sort by crypted field

[inmisia]

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]

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]

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]

Yes. You can change the sql query programmatically.
http://xataface.com/wiki/sql_delegate_method

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]

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!