0

I'm creating a user registration form and when I get to confirming the user's email by emailing them, I need some sort of unique string to confirm against.

Instead of generating one in PHP and inserting it into the database, I wanted to try and add a column to my table that would hold a unique value that I could use whenever I needed to confirm something.

What I want to do is set the value to an MD5 of the current timestamp. I tried just doing SELECT MD5(CURRENT_TIMESTAMP) in phpMyAdmin just to see if it would let me and it did so I thought I'd add that to an update condition but it doesn't seem to be letting me.

ALTER TABLE users ADD confirmation VARCHAR(40) DEFAULT NULL ON UPDATE MD5(CURRENT_TIMESTAMP);

The above is what I've tried. I get an error and I don't know how else to do it.

Is there anyway I can do this or something similar? Side question, does the ON UPDATE trigger on a row that just got inserted?

Spedwards
  • 4,167
  • 16
  • 49
  • 106
  • You can not do that, default value should be constant. Take a look at this thread http://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql, you might consider to create a function and trigger for that – George Sharvadze Jun 15 '15 at 12:46

2 Answers2

0

Use a universal unique identifier for this purpose. It's a 128-bit unique number; it's designed for this kind of thing.

It has a string representation that fits in 36 bytes.

 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

You can generate these things in most programming languages. In MySQL you use the UUID() function to get one. Every time you call UUID(), you're guaranteed to get a new value.

Add a CHAR(36) column to your database, or just use your VARCHAR(40) column.

You can't use data definition language (ALTER TABLE) to declare ON UPDATE except for a native timestamp. You'll need application code to set your UUID values, just like you do for MD5(CURRENT_TIMESTAMP).

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • `Every time you call UUID(), you're guaranteed to get a new value.` Probably being a bit pedantic here, but this isn't true. The intention is that each UUID is unique, but there is no guarantee. – Eborbob Jun 15 '15 at 14:10
0

The syntax you are trying to use doesn't exist. It looks like you are thinking of ON UPDATE CURRENT_TIMESTAMP but that is a rather specific command, as per https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP.

So the ON UPDATE clause only works with CURRENT_TIMESTAMP and only on fields of type TIMESTAMP.

If you want to use the MD5 of the current timestamp either set a trigger, or just manually set the value (e.g. UPDATE users SET confirmation=MD5(CURRENT_TIMESTAMP()) WHERE user_id=123).

Bare in mind that the MD5 of the current timestamp is something that could be quite easily guessed / brute forced, so don't rely on it for security.

Eborbob
  • 1,905
  • 1
  • 15
  • 30
  • Do you know if there's a way to create a single trigger for both `INSERT`s and `UPDATE`s? – Spedwards Jun 16 '15 at 03:57
  • 1
    @Spedwards Not possible in MySQL, see http://stackoverflow.com/questions/1318224/mysql-fire-trigger-for-both-insert-and-update for more details. Think twice before using triggers - they can be useful and the most appropriate method, but equally can be dangerous and lead to lots of problems down the line. – Eborbob Jun 16 '15 at 06:26