mysql 使用if语句,MYSQL：使用if语句的过程

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.

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!

解决方案

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 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.

A comment on your code

IF(@PassOld == in_PassOld) THEN //incorrect

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