0

I'm trying to create a temporary login link URI to be used to gain "viewable" access to a secured area using mysql alone. The idea is to set the value of a field to a given sha1 value.

The sha1 value would be determined by concatenating a couple of random fields and a salt. (I'm not sure if this is good practice)

The following returns an syntax error which I'm fairly sure is to do with using the same table name in the subquery. Is there a sensible way to achieve this? I'm able to do this in PHP, but I'm trying to develop my own SQL skills.

Any recommendations on 'best practice' also appreciated.

update rsvp set rsvp.rsvpViewHash = sha1(select sha1(concat(rsvp.rsvpURLSlug, rsvp.rsvpCreated, 'salt')) from rsvp);
kirgy
  • 1,567
  • 6
  • 23
  • 39
  • try `update rsvp set rsvp.rsvpViewHash = cast( sha1( concat( rsvp.rsvpURLSlug, rsvp.rsvpCreated, 'salt' ) ) as char(40)) from rsvp);` – Bernhard Döbler Feb 19 '14 at 15:56

1 Answers1

1

A couple of problems:

  1. You were hashing twice. If you're that concerned about the security of the hash, use a different algorithm. SHA1 is known to be unsuitable for cryptographic applications, so use SHA256 instead.
  2. Your subquery had no conditions, so it was going to attempt to concat and sha1 on every row in your table, probably not what you wanted. That was also likely the source of the syntax error; a subquery that returns multiple rows probably isn't a suitable argument for sha1(), but I'm not familiar enough with mysql to know that for a fact.

In any case, there's no need for a subquery at all. Simplify like so:

UPDATE rsvp
SET
  rsvpViewHash = CAST(sha1(concat(rsvpURLSlug, rsvpCreated, 'salt')) AS CHAR(10000) CHARACTER SET utf8)
WHERE
  -- conditions
;

EDITED

Cast SHA result from blob to char; assumes destination field rsvpViewHash is capable of storing a CHAR(10000)

Palpatim
  • 9,074
  • 35
  • 43
  • The sha1 function seemingly returns a blob. One thus might want to cast it to char. Cast seems to return the 40 char HEX string. – Bernhard Döbler Feb 19 '14 at 16:03
  • Thanks @Palpatim I had made a double mistake of sha1 twice (this was not intentional at all) but also I never thought it possible to set a value using the current record's columns without the need for a subquery. This worked perfectly, thanks for your help. – kirgy Feb 19 '14 at 17:13
  • - On a side note, Im storing my sha1 as CHAR(40) in hex as this was the best recommendations I could find online. See here: http://stackoverflow.com/questions/614476/storing-sha1-hash-values-in-mysql – kirgy Feb 19 '14 at 17:15
  • 1
    The docs say: `As of MySQL 5.5.3, their return value is a nonbinary string that has a character set and collation` CAST might therefore be left off when working with a current MySQL server version. A SHA1 hash has a fixed length of 40 hex digits http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_sha1 – Bernhard Döbler Feb 19 '14 at 17:20