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!

Never ever store passwords in the clear in a database. Use a salted hash instead, see here for the why: http://stackoverflow.com/questions/2283937/how-should-i-ethically-approach-user-password-storage-for-later-plaintext-retriev and here for the how: http://stackoverflow.com/questions/3273293/salting-my-hashes-with-php-and-mysql — Johan, Sep 27 '11 at 09:59

score 11 · Accepted Answer · edited May 23 '17 at 10:30

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 :-)

The pass is already salted. The routine was for me :) So i know how to write it. — Linqan, Sep 27 '11 at 10:15
Ive tried to remove the salt stuff and just go for the IF statement, but it wont work for me — Linqan, Sep 27 '11 at 10:23
Don't forget to set `delimiter ;` after you're done with the stored proc, otherwise if will not run, because it's using the wrong delimiter inside. — Johan, Sep 27 '11 at 12:46

MYSQL: Procedure with if statement

1 Answers1

Linked