Select id by md5??

[eluser]mvdg27[/eluser]
Hey guys,

I'm having a little problem with a query .. and actually I'm not sure if what I want, is even possible:

I want to run a simple query to get the right user data for a specific ID. Something like this:

Code:

$this->db->where('id', $member_id);
$query = $this->db->get('table');
return $query->first_row();

But instead of using the real member_id, I wanted to use the md5 of it. For example instead of passing '45' for an id, I want to pass '9180872cd65e547e3aadd3a8f0ab4b17'. So basically I want to query for the row where the md5-ed id equals $member_id ... Is this even possible?

For those of you wondering why I want this .. I don't want the results to be 'browsable' by the users, by simply changing a segment in the url.

One solution off course is to add an extra column to my table .. but I first want to see if I can do it with just a query.

Thanks!

[eluser]davidbehler[/eluser]
Try

Code:

$this->db->where('md5(id)', $member_id);

or

Code:

$this->db->where('md5(id)', $member_id, FALSE);

One of them should work.

[eluser]mvdg27[/eluser]
Thanks!! This was too simple

This one works for me by the way:

Code:

$this->db->where('md5(id)', $member_id);

Cheers, Michiel

[eluser]jedd[/eluser]
While it works, you might want to re-design the approach - I think this will be a very expensive call. Have you tried doing some profiling on it with a substantial number of rows in that table?

[eluser]mvdg27[/eluser]
I see .. I haven't done any profiling yet, but I can imagine it causing some load on the database yes. I think adding an extra column to the table, might be a wiser approach.

[eluser]TheFuzzy0ne[/eluser]
Or you can encrypt and decrypt using the Encrypt library, then the encoding/decoding will only ever be done once in any given transaction. The only problem that causes, is that some of the characters in the URL are not allowed by CodeIgniter. However, I've written some code to make encrypted strings URI-safe. http://ellislab.com/forums/viewthread/109429/

[eluser]slowgary[/eluser]
Just out of curiosity, what's the benefit (or purpose even) of hashing the id?

[eluser]Aea[/eluser]
Hashing the ID gives you a very basic protection against knowledge of how big your database is and makes "fishing" for IDs more difficult. Of course it being MD5 the protection is basically none, if you want to do this write your own key generator that's non-deterministic (i.e. don't base it off your ID unless you've got a good grasp of cryptology).

If you have to do it by ID make an column for the MD5, the call is VERY expensive, although on small applications you'll feel no difference.

[eluser]jedd[/eluser]
[quote author="slowgary" date="1238457092"]Just out of curiosity, what's the benefit (or purpose even) of hashing the id?[/quote]

Because it will appear in the URL, it prevents the user adding 1 (etc) to the interesting segment, and seeing other things they should not see.

So, yes, it's security by obscurity (which we don't like, of course) but is so much easier than writing code that is genuinely secure.

[eluser]Aea[/eluser]
This will work for smaller sites...

$intContentKey = mt_rand(100000, 999999).mt_rand(100000, 999999);

.. Check For Duplicates ...

.. Insert, Use Instead of ID ...

Protip:
Factor in date somehow to avoid collisions, but still make sure it's a unique!
Factor in your content ID somehow so you can identify content type from a key.
Factor in your DB ID if you're using multiple DB servers.

There's actually quite a large list of factors when it comes to making good keys, but unless you've got a large site they will be irrelevant and perfectionist design, cross the bridge when you get to it.

Edit: Or if quick and ugly is okay...

http://us3.php.net/uniqid

I'd do some post processing to remove spaces