Need to sort by encrypytrf field

Greg K posted this at 21:04 — 1st January 2004.

Joined: Nov 2003

Ok, I'm at my first project that will save thinks such as cleint's employee info on our website. I have started researching the best way to encrypt the data to store in database, and think I'm ready to give it a try, however I just realized something.

Say I have the page that lists employees, and I want to sort them by Name, however, for protection, the data is encrypted before saving...

Now if there was wasn't the problem of possible Paging of the data (displaying x # items per page), then I would just grab all the data, decrypt it before putting it in a array and then sort it then.

Anyone with recommendations on this, or any references to good tutorials on this type of situation, would be very appriciated.

Server Info: FreeBSD/Apache/PHP/mySQL

-Greg

Mark Hensler posted this at 18:19 — 5th January 2004.

He has: 4,048 posts

Joined: Aug 2000

The easiest way would be to not encrypt the data you need to sort by.

Or consider...

CREATE TABLE `tmp` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `str` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

INSERT INTO tmp SET str=ENCODE("one", "key");
INSERT INTO tmp SET str=ENCODE("two", "key");
INSERT INTO tmp SET str=ENCODE("three", "key");
INSERT INTO tmp SET str=ENCODE("four", "key");
INSERT INTO tmp SET str=ENCODE("five", "key");

SELECT id, DECODE(str, "key") AS str FROM tmp ORDER BY str ASC;

+----+-------+
| id | str   |
+----+-------+
|  5 | five  |
|  4 | four  |
|  1 | one   |
|  3 | three |
|  2 | two   |
+----+-------+

Mark Hensler
If there is no answer on Google, then there is no question.

antoshka posted this at 05:50 — 23rd January 2004.

They have: 30 posts

Joined: Aug 2003

there is a method of indexing data (forgot what it's called) that can somewhat help you. here's a simplified version:

create a new field in the table. if an employee name starts with an A, assign 1 to the field. if it's a B, assign 2. and so forth. then, sort by this new field when SELECTing from the DB. it will not always yield precise results (depends), but still it's something.

you can play around with it. create several new fields, each for an encrypted data field. you can have several numbers there. for instance, look at this correspondence:

AA - 1
AB - 2
AC - 3
...
AZ - 26
BA - 27
BB - 28
BC - 29
BD - 30
...
BX - 50
BY - 51
BZ - 52
CA - 53
CB - 54
...

you see the pattern