SQL SHA1 inside WHERE

Question

In my program, we store a user's IP address in a record. When we display a list of records to a user, we don't want to give away the other user's IP, so we SHA1 hash it. Then, when the user clicks on a record, it goes to a URL like this:

http://www.example.com/allrecordsbyipaddress.php?ipaddress=SHA1HASHOFTHEIPADDRESS

Now, I need to list all the records by the IP address specified in the SHA1 hash. I tried this:

SELECT * FROM records 
WHERE SHA1(IPADDRESS)="da39a3ee5e6b4b0d3255bfef95601890afd80709"

but this does not work. How would I do this?
Thanks, Isaac Waller

Answer 1

Don't know if it matters, but your SHA1 hash da39a3ee5e6b4b0d3255bfef95601890afd80709 is a well-known hash of an empty string.

Is it just an example or you forgot to provide an actual IP address to the hash calculation function?

Update:

Does your webpage code generate SHA1 hashes in lowercase?

This check will fail in MySQL:

SELECT  SHA1('') = 'DA39A3EE5E6B4B0D3255BFEF95601890AFD80709'

In this case, use this:

SELECT  SHA1('') = LOWER('DA39A3EE5E6B4B0D3255BFEF95601890AFD80709')

, which will succeed.

Also, you can precalculate the SHA1 hash when you insert the records into the table:

INSERT
INTO    ip_records (ip, ip_sha)
VALUES  (@ip, SHA1(CONCAT('my_secret_salt', @ip))

SELECT  *
FROM    ip_records
WHERE   ip_sha = @my_salted_sha1_from_webpage

This will return you the original IP and allow indexing of ip_sha, so that this query will work fast.