mysqlprocedure

String Paramater in MySQL Stored Procedure


I have what I hope is a simple question involving creating the SELECT query with a string parameter in a MySQL stored procedure and subsequently passing a CALL query from within PHP. This is from a login page and is therefore calling the procedure for login. 'Wowcrofty' in the error is the username. The stored procedure I am using is included below.

DELIMITER //
CREATE PROCEDURE retrieve_user_info (IN username VARCHAR(50))
BEGIN
SELECT * FROM db.dbo WHERE user_username=username;
END //
DELIMITER ;

This is how I am calling the procedure from PHP.

$result = $conn->query("CALL retrieve_user_info($enteredusername)");

I have loaded the page containg the query and this is the error I am receiving.

Uncaught mysqli_sql_exception: Unknown column 'wowcrofty' in 'field list'

I have created a few other stored procedures and they are working perfectly. However, this is the first one that uses a string as a parameter. What am I doing wrong? Thank you for any suggestions.


Solution

  • Quote the parameter:

    query("CALL retrieve_user_info('$enteredusername')");
    

    Or better, use the library to prevent SQL injection:

    stmt = $connection->prepare("CALL retrieve_user_info(?)");
    $stmt->bind_param("s", $enteredusername);
    $stmt->execute();
    $result = $stmt->get_result();