Sorting encrypted PHP object

Question

I have a user table which I've encrypted and stored in a MySQL database.

When pulling the data back out and adding it to a form via a select input control I'd like to be able to order the items in alphabetical order.

I am decypting the items on each iteration of a foreach loop.

<?php foreach ($data['crewData'] as $crew) : ?>
   <option value="<?php echo $inputCrew; ?>">
      <?php echo decryptData($crew->user_firstName).' '.decryptData($crew->user_lastName); ?>
   </option>
<?php endforeach; ?>

I know I can sort an array using sort($variable); but the data being returned by $data['crewData'] is a standard object as the snippet below shows...

array(38) {
  [0]=>
  object(stdClass)#18 (21) {
    ["user_id"]=>
    string(2) "20"
    ["user_firstName"]=>
    string(59) "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

I'd like to sort by the user_firstName field. My only thought as to how to do this is run a foreach loop decrypting the data, creating a temporary non-encrypted object then presenting this to the select.

I only have around 50 users so far so the overhead isn't too bad but as the user count increases I can see this being a pain??

To go the above route will this work?

$crewData = $this->userModel->getUserByType('Crew');
$array = [];
foreach ($crewData as $item) {
   $firstName = decryptData($item->user_firstName)  //decryptData() is my decryption function
   array_push($array, $firstName );
}
$arraySorted = sort($array);

Decrypting each individual property at runtime seems a bit awkward. Could you not create a view over your table which does the decryption and then just select from the view? — waterloomatt, Dec 04 '20 at 14:18
Or move your decryption logic up into your models so by the time you get an array of models they're already decrypted. — waterloomatt, Dec 04 '20 at 14:22
`To go the above route will this work?` - yes, that will work but you may regret going that route as your application grows. Each change to your table will spawn _n_ changes in PHP (views, controllers, models etc.) — waterloomatt, Dec 04 '20 at 14:56

score 2 · Accepted Answer · answered Dec 04 '20 at 14:47

I don't know much about your requirements or setup but one option is to move your decryption logic up into your database. This actually gives you more control and security as you can apply permissions to who can run the decrypt function and who can execute the users_decrypted view.

This is just some pseudo code, but it would look something like.

// Create a function to do the decryption
CREATE FUNCTION decrypt (input VARCHAR) 
RETURNS VARCHAR

BEGIN

   DECLARE decrypted VARCHAR;

   SET decrypted = '';

   // your decryption logic

   RETURN decrypted;

END;

// Create your view which returns the decrypted values
CREATE VIEW users_decrypted AS

    SELECT first_name, last_name FROM (
        SELECT decrypt(first_name) as first_name
            , decrypt(last_name) as last_name
            , ...
        FROM users
    ) ORDER BY first_name;

And then in your models, you would use the view and don't have to worry about decrypting the actual data.

<?php
$query = 'SELECT first_name, last_name FROM users_decrypted';
...

Alternatively, if you want to keep your decryption logic in PHP you could move it all up into your models so your controllers and view don't have to deal with it. It is here where you would do your sorting.

IMO, this solution is slower, more complicated and will require more coding and maintenance in the long run.

class User extends Model
{
    public function getUsers()
    {
        $users = 'SELECT first_name, last_name,... FROM users';

        ...

        $listOfKeysToDecrypt = ['first_name', 'last_name', ...];

        // This actually modifies the array which may not be a good idea.
        // You could always create a new array instead.
        foreach ($user as $key => $value) {
            if (in_array($key, $listOfKeysToDecrypt)) {
                $user[$key] = decrypt($value);
            }
        }

        // $user is now decrypted so you can sort it
        usort($users, ...);

        return $users;
    }

}

Thanks @waterloomatt - I'm using libsodium at the moment to encrypt / decrypt. I need to learn more about MySQL encryprition / decryption but does it work simarlly to libsodium, I'm assuming the MySQL route might be more secure as you mentioned in your answer? — Matthew Barraud, Dec 04 '20 at 15:28
libsodium is a great library so continue to use it if that's what you want. My suggestion would be to move your encryption/decryption logic up into your model so you don't have to deal with individual properties in your controllers/views. — waterloomatt, Dec 04 '20 at 16:02

Sorting encrypted PHP object

1 Answers1