Reverse hash lookup query

Question

i have an web service and one of the parameter our clients needs to use is a custom key. this key is a hash of sha1

eg:
bce700635afccfd8690836f37e4b4e9cf46d9c08

then when the client call our web service i have to check few things:

• is client active
• is client can submit via webservice and service

now my problem is this:

i have a query:

$sql = "SELECT permission, is_active FROM clients WHERE sha1(concat(id,key)) = '" . mysql_real_escape_string($key) . "'";

Am i doing the right thing? or there's a better way? thanks

Answer 1

This approach is expensive, since, every time you run this query, MySQL will have to examine every single record in clients and compute the SHA-1 hash of its id and key. (I'm assuming here that clients has more than a few rows, or at least, that you'd like to use an approach that supports the case where clients has more than a few rows.)

Why don't you add a new field called (say) id_key_sha1? You can use a trigger to keep the field populated, and add an index on it. This approach should perform much better.

Edited to add: You mention that the client, in addition to passing in this SHA-1 hash, also has to submit a username and password? I don't know what your table structure looks like, but I'm guessing that it would make more sense to find the client record based on the username first, and then comparing the SHA-1 hash for that specific record, rather than trying to find the record by the SHA-1 hash.