How can I bind when selecting query from sqlite database?

Question

I'm connecting to sqlite in my db file, Then I created this function to access it from my functions file:

function db(){
    require(getBasePath('db/connection.php'));
    return $db;
}

This is what I'm doing and I get error node must be an int. My goal is take what what was posted and check it against my table in db and if user exists redirect to a booking page which I've created.

function login($request)
{

    $username = $request['username'];
    $password = md5($request['password']);
    try {
        $sql = db()->query("SELECT
        *
        FROM admin_user AS user
        WHERE user.email = :username AND user._password = :pass",[
            'username' => $username,
            'pass' => $password
        ]);
        $result = $sql;
    } catch (Exception $e) {
        echo "Unable to fetch row " . $e->getMessage();
        exit;
    }

    if ($result->fetchAll()[0]['id'] < 1) {
        throw new Exception('User does not exist');
    }
    $_SESSION['user_id'] = $result->fetchAll()[0]['id'];

    return header('Location: ' . getAppUrl());
}

I've also tried using PDOStatement::bindParam, PDOStatement::bindValue, PDO::prepare instead of query() etc and still searching. ...

When I do the below, it works but I want to insert placeholders into an SQL statement instead of hardcoding what I'm checking for, I hope that makes sense.

function loadData()
{
 
    try {
        $sql = db()->query("SELECT
        `user`.*,
        `worker`.*,
        `_address`.*
        FROM `recipient` AS `user`
        INNER JOIN `care_worker` AS `worker`
        ON `worker`.`id` = `user`.`care_worker`
        INNER JOIN `recipient_address` AS `_address`
        ON  `user`.`care_worker` = '1'");
        $result = $sql;
    } catch (Exception $e) {
        echo "Unable to fetch row " . $e->getMessage();
        exit;
    }

    return $result->fetchAll();
}

Please help, Thank you! :)

Side note: [don't use algorithms like `md5` or `sha1` to encrypt passwords](https://stackoverflow.com/a/11168596/4205384), use PHP's built-in [`password_hash`](https://www.php.net/manual/en/function.password-hash) and [`password_verify`](https://www.php.net/manual/en/function.password-verify) to store and check passwords. — El_Vanja, Nov 20 '20 at 18:37
@AbraCadaver That's just his login function parameter: `function login($request)` — El_Vanja, Nov 20 '20 at 19:09

score 0 · Answer 1 · answered Nov 21 '20 at 14:37

After tracking back my steps and doing more research on PHP's Built-In mysqli functions, I got my answer :)

function login($request)
{

    $username = $request['username'];
    $password = password_hash($request['password'], PASSWORD_DEFAULT);

    $sql = db()->query("SELECT
    *
    FROM admin_user AS user
    WHERE user.email = :email AND user._password = :pass");
    $sql->bindParam('email', $username, PDO::PARAM_STR);
    $sql->bindParam('pass', $password, PDO::PARAM_STR);
    $sql->execute();
    $result = $sql->fetchAll();

    if (count($result) < 1) {
        throw new Exception('User does not exist');
    }

    $_SESSION['user_id'] = $result[0]['id'];;

    return header('Location: ' . getAppUrl());
}

How can I bind when selecting query from sqlite database?

1 Answers1