有没有办法通过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)

Answer 1

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.