Like the title says, I'm trying to implement the programmatic parts of RFC4226 "HOTP: An HMAC-Based One-Time Password Algorithm" in SQL. I think I've got a version that works (in that for a small test sample, it produces the same result as the Java version in the code), but it contains a nested pair of hex(unhex()) calls, which I feel can be done better. I am constrained by a) needing to do this algorithm, and b) needing to do it in mysql, otherwise I'm happy to look at other ways of doing this.

What I've got so far:

  -- From the inside out...

  -- Concatinate the users secret, and the number of time its been used
  -- find the SHA1 hash of that string
  -- Turn a 40 byte hex encoding into a 20 byte binary string
  -- keep the first 4 bytes
  -- turn those back into a hex represnetation
  -- convert that into an integer
  -- Throw away the most-significant bit (solves signed/unsigned problems)
  -- Truncate to 6 digits
  -- store into otp
  -- from the otpsecrets table

  select (conv(hex(substr(unhex(sha1(concat(secret, uses))), 1, 4)), 16, 10) & 0x7fffffff) % 1000000
    into otp
    from otpsecrets;

Is there a better (more efficient) way of doing this?