(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)
（设置场景） 我在我的数据库中有一个表，其中包含用户密码（密码列） MD5在插入新密码时通过触发器加密。 此表还包含用户在其网站帖子上的喜欢量（喜欢列）。 该表还有一个触发器，用于在进行更新时将密码设置为md5。 这已经到位，以便当用户编辑他们的个人资料时，他们可以更改他们的密码，并且需要再次加密。
（主要问题） 我遇到的问题是，当其他用户点击帖子上的“喜欢”按钮时，会更新用户喜欢添加的内容1.问题是我的密码列 然后md5再次加密，我不想发生。
（你可以做什么！） 所以我希望的是有一种方法可以指定（在mysql中） ）触发效果的列是什么。 当like列更新时，不应在密码列中更改任何内容。 但目前密码仍然被加密。
任何帮助都将不胜感激！
我已经尝试通过php将密码设置为null 。 然后从数据库中获取密码并再次插入！ 但是它已经加密了，所以当它返回到数据库时触发器就会对已经被哈希过的密码进行md5命令。
这是我能用的唯一代码 显示哪个位于触发框中
SET NEW.Password = MD5（NEW.Password）
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.