MYSQL：使用if语句的过程 [英] MYSQL: Procedure with if statement

问题描述

我正在尝试创建一个首先检查用户密码的例程，如果它是正确的，它将从不同的表返回一些值或更改一行中的某些值。

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.

如果不在PHP中处理两个查询，这是否可行？首先要求输入密码，检查是否正确，然后允许用户更改名称。

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.

这里有一个使用电子邮件和密码获取用户行的示例。

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

感谢所有帮助！

推荐答案

你应该真正散列这些密码，使用以下代码

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 ;

salt 是表格中的额外字段用户或多或少随机，但不需要保密。它可以击败彩虹表。

您可以将盐设置为a短字符串char（10）或随机数据。例如

The salt is an extra field in table user that 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 = ROUND(RAND(unix_timestamp(now())*9999999999);

您不需要更新盐，只需生成一次然后存储它。

You don't need to update the salt, just generate it once and then store it.

有关此问题的更多信息，请参阅：

Salting我的PHP和MySQL哈希值

我应该如何道德地接近用户密码存储以便以后的明文检索？

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?

对您的代码的评论

IF(@PassOld == in_PassOld) THEN  //incorrect 
IF(@PassOld = in_PassOld) THEN   //correct, SQL <> PHP :-)

这篇关于MYSQL：使用if语句的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！