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: stackoverflow.com/questions/2283937/… and here for the how: stackoverflow.com/questions/3273293/… — Johan
– Johan, Commented Sep 27, 2011 at 9:59

Community · Accepted Answer · 2017-05-23 10:30:52Z

11

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

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Sep 27, 2011 at 10:11

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Linqan Over a year ago

The pass is already salted. The routine was for me :) So i know how to write it.

Linqan Over a year ago

Ive tried to remove the salt stuff and just go for the IF statement, but it wont work for me

Johan Over a year ago

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.

Collectives™ on Stack Overflow

MYSQL: Procedure with if statement

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related