MYSQL: Procedure with if statement

Question

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!

Answer 1

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.

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?

A comment on your code

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