Jump to content

stored procedure for php pdo login


jodunno

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.

Link to comment
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
}

 

Link to comment
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>

Link to comment
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.

Link to comment
Share on other sites

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.

Link to comment
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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.