MYSQL：带有 if 语句的过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7566948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL: Procedure with if statement
提问by Linqan
I'm trying to make a routine that first checks a users password, if it's correct it shall return some values from a different table or change some values in a row.
我正在尝试创建一个例程，首先检查用户密码，如果正确，它将从不同的表中返回一些值或连续更改一些值。
Is this even possible without making two queries that you handle in PHP? First call for the password, check if its correct then allow the user to make the name change.
这甚至可能不进行您在 PHP 中处理的两个查询吗？首先调用密码，检查它是否正确，然后允许用户更改名称。
Here an example of getting the Rows in User with email and password.
这是使用电子邮件和密码获取用户中的行的示例。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_info`(
IN in_Email VARCHAR(45),
IN in_Pass VARCHAR(45)
)
BEGIN
SELECT * FROM User WHERE Email = in_Email AND Pass = in_Pass;
END
And this is what Ive got so far:
这就是我到目前为止所得到的：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `change_pass`(
in_Email VARCHAR(45),
in_PassOld VARCHAR(45),
in_PassNew VARCHAR(45)
)
BEGIN
SET @PassOld = (SELECT Pass From User WHERE Email = in_Email);
IF(@PassOld = in_PassOld) THEN
UPDATE User SET Pass = in_PassNew WHERE Email = in_Email;
END IF;
ENDND IF;
END
Thanks for all the help!
感谢所有的帮助！
回答by Johan
You should really hash those passwords, use the following code
您应该真正散列这些密码，使用以下代码
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `change_pass`(
in_Email VARCHAR(45),
in_PassOld VARCHAR(45),
in_PassNew VARCHAR(45)
)
BEGIN
DECLARE KnowsOldPassword INTEGER;
SELECT count(*) INTO KnowsOldPassword
FROM User
WHERE Email = in_Email AND passhash = SHA2(CONCAT(salt, in_PassOld),512);
IF (KnowsOldPassword > 0) THEN
UPDATE User
SET Passhash = SHA2(CONCAT(salt, inPassNew),512)
WHERE Email = in_Email;
END IF;
END $$
DELIMITER ;
The saltis an extra field in table userthat is more or less random, but does not need to be secret. It serves to defeat rainbow tables.
You can set salt to a short string char(10) or randomish data. e.g.
该salt是在表中的额外字段user是或多或少随机的，但不必是秘密的。它用于击败彩虹表。
您可以将 salt 设置为短字符串 char(10) 或随机数据。例如
salt = ROUND(RAND(unix_timestamp(now())*9999999999);
You don't need to update the salt, just generate it once and then store it.
您不需要更新盐，只需生成一次然后存储它。
For more on this issue see:
Salting my hashes with PHP and MySQL
How should I ethically approach user password storage for later plaintext retrieval?
有关此问题的更多信息，请参阅：
使用 PHP 和 MySQL 对我的哈希进行加盐处理我
应该如何从道德上处理用户密码存储以供以后进行明文检索？
A comment on your code
对您的代码的评论
IF(@PassOld == in_PassOld) THEN //incorrect
IF(@PassOld = in_PassOld) THEN //correct, SQL <> PHP :-)
