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
– waterloomatt, Commented Dec 4, 2020 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
– waterloomatt, Commented Dec 4, 2020 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
– waterloomatt, Commented Dec 4, 2020 at 14:56

waterloomatt · Accepted Answer · 2020-12-04 14:47:44Z

2

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;
    }

}

answered Dec 4, 2020 at 14:47

waterloomatt

3,7731 gold badge23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matthew Barraud Over a year ago

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?

waterloomatt Over a year ago

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.

Collectives™ on Stack Overflow

Sorting encrypted PHP object

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related