CODEWITHSUNDEEP
phpmysqlstored-procedurespdo
Hobbyist
Hobbyist

Reputation: 16182

MySQL Stored procedure, PDO::fetch() throws general error on null return

The problem is that I'm using a stored procedure to handle validation of my accounts. (Please note that this is a dumbed down version of the procedure and method and is only used for demonstration purposes). Note: Account authentication is not the original purpose of this method, once again just for example.

Here's my Example MySQL procedure:

BEGIN
    DECLARE rc INT(11);
    DECLARE new VARCHAR(40);
    SET new = uuid();

    UPDATE accounts SET session_key = new WHERE account_id = id_in AND session_key = session_in;

    SELECT ROW_COUNT() into rc;

    IF(rc > 0) THEN
        SELECT new AS `session_key`;
    END IF;

END

Here's the PHP related code that goes with it:

    private static function authenticate() {
        $connection = Database::getConnection();
        $account = 19;
        $session = "cc73e13b-2983-11e5-8ade-00163e603fb4";
        $statement = $connection->prepare("CALL AUTH_SESSION(:account, :session);");
        $statement->bindParam(":account", $account);
        $statement->bindParam(":session", $session);
        if($statement->execute()) {
            if($row = $statement->fetch()) {
                echo 'valid session';
            } else {
                echo 'invalid session';
            }
        } else {
            echo 'failed to execute query';
        }
    }

When the session_key and account_id are correct, the procedure prints out (or SELECTS) the new session_key so that PHP can obtain it. However when one of the two values are not correct the SELECT statement is never called.

I figured this would be fine. If a row is returned->get the session key (Success) and if the row isn't returned, the validation failed, but it apparently doesn't work that way.

The error returned is as follows:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error'

Just to re-iterate, this error is only thrown when (!(rc>0)).

Upvotes: 0

Views: 731

Answers (1)

Barmar
Barmar

Reputation: 780798

You can only use fetch() if the query returns a result set, i.e. the stored procedure has to return the result of a SELECT.

You could have the procedure return an empty result set when rc > 0 is false.

BEGIN
    DECLARE rc INT(11);
    DECLARE new VARCHAR(40);
    SET new = uuid();

    UPDATE accounts SET session_key = new WHERE account_id = id_in AND session_key = session_in;

    SELECT ROW_COUNT() into rc;

    SELECT new AS session_key
    FROM dual
    WHERE rc > 0;

END

When you call fetch() it will return false if the result set is empty, otherwise it will return an array containing the session key.

Upvotes: 1

Related Questions