Jump to content
jodunno

stored procedure for php pdo login

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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
}

 

Share this post


Link to post
Share on other sites

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>

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ;

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by jodunno

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.