Is there a way to specify what column is updated though a trigger in mysql?

Question

(Setting the scene) So I have a table in my database which holds the users password(Password column) which has been encrypted by MD5 via a trigger when they insert their new password. This table also holds the amount of likes(Likes column) that a user has on their website posts. The table also has a trigger to md5 the password when an update is made. This was put in place so that when the user edits their profile they can change their password and it needs to be encrypted again.

(Main issue) The issue I have is that when other users click a like button on a post it updates a users likes to add 1. The problem with this is that my password column is then md5 encrypted again which I don't want to happen.

(What you can do!) So what I'm hoping for is that there's a way to specify (in mysql) what column the trigger effects. When the like column updated nothing should be changed in the password column. But at the moment the password keeps getting encrypted.

Any help would be much appreciated!

I've tried setting the password to null through php. Then to get the password from the database and insert it again! But it's already encrypted so that when it goes back into the database the trigger is effected the md5 command is done on a password that has already been hashed..

This is the only code I can show which is in the trigger box

SET NEW.Password = MD5(NEW.Password)

It would be better to convert the code to use [`password_hash()`](https://stackoverflow.com/questions/30279321/how-to-use-password-hash) instead. — Nigel Ren, Jul 12 '19 at 19:31
Don't use a trigger. Don't use MD5 ([yikes](https://www.google.com/search?q=md5+password+cracker&rlz=1C1GCEA_enUS833US833&oq=md5+password+cracker&aqs=chrome.0.0l6.4121j0j1&sourceid=chrome&ie=UTF-8)). Do your [password hashing in php](https://www.php.net/manual/en/function.password-hash.php) before the insert. — JNevill, Jul 12 '19 at 19:39
Also [PHP's manual: Safe Password Hashing](https://php.net/manual/en/faq.passwords.php) which explains more.. — Raymond Nijland, Jul 12 '19 at 19:51
@NigelRen Thanks I'll give that a go! Never heard of it but thank you! I'm knew to this world lol! How would I use it when taking a users login credentials on their password? Like so? $pass = password_hash($conn->real_escape_string($_POST['postpass'])); Would that work? — James Clark, Jul 12 '19 at 19:51
`real_escape_string()` is not safe in all situations see [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) .. Also it "forces" you to use and trust on MySQL auto type conversion consider id column is a INT you would have to write `id = '.real_escape_string($id).'` which makes it a string type as writing `id = real_escape_string($id)` is not safe which makes sense because you qoute a int not a string.. Also misused datatypes can prevent MySQL to use indexes also.. — Raymond Nijland, Jul 12 '19 at 19:59
...This mistake is very easy to make use prepared statements like the link in mine other comment explains then you can't go wrong ... — Raymond Nijland, Jul 12 '19 at 19:59
Possible duplicate of [SHA1 vs md5 vs SHA256: which to use for a PHP login?](https://stackoverflow.com/questions/2235158/sha1-vs-md5-vs-sha256-which-to-use-for-a-php-login) — Dharman, Jul 12 '19 at 21:24

score 0 · Accepted Answer · answered Jul 12 '19 at 22:52

Here is a version with using CRYPT and a seed starting with $6$ makes a SHA512 with seed.

DELIMITER //

CREATE TRIGGER `before_update_user` BEFORE UPDATE ON `user`
 FOR EACH ROW BEGIN
     if ( OLD.Password != NEW.Password ) then
         -- password has changed, encrypt
         SET NEW.Password = ENCRYPT(NEW.Password, CONCAT('$6$', SHA2(RANDOM_BYTES(64), '256')));
     end if;
END
//

The comment about not using MD5 is a good advice. More on using CRYPT on security SX. Be warned that Crypt is to be removed from MySQL from 8.0.3 and they have no salted versions so you need to roll your own salt and SHA2 if you want to make a save password in a trigger. Thus it seems doing the hashing in PHP might be the safer option.

There is nothing wrong with combing triggers and prepared statements. I have no idea why someone would assume using triggers implies prepares statements are off the table. Always use prepared statements.

Is there a way to specify what column is updated though a trigger in mysql?

1 Answers1