stored procedure for php pdo login

[jodunno]

Dear forum members, i am new to php, pdo and mysql. I have created a working login script/database. I read alot of material about this subject. Yes, i use hashed passwords and htmlentities() for display. Anyway, i am trying to implement a stored procedure for my login script. I cannot find good enough material about this subject as it should pertain to a login.

 

here is what i have:
 

$dbh = new PDO(etc etc);

$query = 'SELECT id, username, password FROM user_accounts WHERE username = :inputUsername';

$stmt = $dbh->prepare($query);

$stmt->execute(array(':inputUsername' => $username));

$results = $stmt->fetch();

then i assign variables to the fetched data. i compare username and password, if accepted, set session variable for login.

 

i want to convert this to a stored procedure. I understand to type the following data but i don't see how it returns the data with a fetch.
 

DELIMITER $$

CREATE PROCEDURE validateUser()

BEGIN

SELECT id, username, password

FROM user_accounts

WHERE username = // what do i put here to pass a variable? // ;

END $$

DELIMITER ;

 

Will someone please offer a clue? i don't know how to make this work. I just want to return the id, username and password for validation based upon whether or not a username exists.

 

Thank you.

[Barand]

try

DELIMITER $$

CREATE PROCEDURE `validateUser`(user varchar(30))
BEGIN
    SELECT id, username, password
    FROM user_account
    WHERE username = user;
END$$

DELIMITER ;

then

$user = $_POST['username'];
$pwd = $_POST['password'];

$res = $db->query("CALL validateUser('$user')");
$user_data = $res->fetch();

if (password_verify($pwd, $user_data['password']) {
    // password and user OK
}

 

[jodunno]

Hello Barand, Thank you for replying. I've tried to enter the procedure that you've posted and i am getting an error 1064 (4200) SQL syntax error. I do not know why i cannot get a procedure to work. I am using xampp with mysql MariaDB if it helps. My terminal shows MariaDB>

[Barand]

What is the whole error message?

[jodunno]

The error is always near the SELECT statement at line 1

[jodunno]

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''validateUser'(user_name varchar(127)) BEGIN SELECT id, username, password F' at line 1

I've tried many different methods and they all fail at the SELECT or FROM statements.

[Barand]

Do you already have a "validateUser" procedure?

If so, try

DROP procedure IF EXISTS `validateUser`;

DELIMITER $$

CREATE PROCEDURE `validateUser`(user varchar(30))
BEGIN
	SELECT id, username, password
    FROM user_account
    WHERE username = user;
END$$

DELIMITER ;

 

[jodunno]

no, i've allready ran a drop just to be sure. HOWEVER, i got mysql to accept the procedure. Now it shows a query ok. Here is what i've typed:
 

DELIMITER //

CREATE PROCEDURE validateUser(IN user_name varchar(127))

BEGIN

SELECT id, username, password FROM user_accounts WHERE username = user_name;

END //

DELIMITER ;

i saw someone used IN with the procedure. is this correct? but i also removed the single quotes surrounding validateUser. Perhaps i should delete the procedure and try it again without IN because i cannot login to my app now. I will tinker with this to see if i can pinpoint a problem.

Edited March 20, 2019 by jodunno

[Barand]

The single quotes were actually backticks, but as validateUser isn't a reserved SQL keyword they are optional.

Also the IN appears to be optional with MySql but I cannot vouch for MariaDB

[jodunno]

well the query is ok so i will just ti nker with the pdo to get the login working. I thank you for your help, Barand. You are very kind to spend your valuable time helping me. At least i got the procedure to be accepted because of your efforts. Thank you again.