Sql prepared statement execute() error

Question

I am trying to use prepared statement for my PHP SELECT query but i get this error

SQLite3Stmt::execute() expects exactly 0 parameters, 1 given in C:\xampp\htdocs\xport\post.php on line 75

What could be wrong here? I ma referencing the answer i got here

But this doesn't work.Below is my code.

$postid = (int) $_GET['post_id'];

$userpostquery = "SELECT * FROM  users WHERE userid = ?";
$stmt = $db->prepare($userpostquery);
$stmt->execute([$postid]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
{
    $cname = $row['cname'];

    echo $cname;
}

Thanks.

`$stmt->execute([$postid]);` remove `[$postid]`. start with that. — Funk Forty Niner, Jul 26 '17 at 12:46
Seems like you are using http://php.net/manual/en/sqlite3stmt.execute.php, not http://php.net/manual/en/pdostatement.execute.php To bind see http://php.net/manual/en/sqlite3stmt.bindvalue.php. — chris85, Jul 26 '17 at 12:46
@Fred-ii- if i do that how would i now get what i am looking for in the `$userpostquery` that is `bindValue ? with #postid`. — diagold, Jul 26 '17 at 12:48
Thanks @chris85. Does this mean i don't need `PDO` when fetching with the `while` loop. Because the current code does'nt fetch.? — diagold, Jul 26 '17 at 12:53
You can't use sqlite **and** PDO, they are different drivers. You need to use all the functions of whichever **one** you are using. — chris85, Jul 26 '17 at 12:54
I feel this falls as a duplicate of [Can I mix MySQL APIs in PHP?](http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — Funk Forty Niner, Jul 26 '17 at 12:56
So replace `$stmt->execute([$postid]);` with `$stmt->execute();` and prior to that `$stmt->bindValue(1, $postid, SQLITE3_INTEGER);` — chris85, Jul 26 '17 at 12:57
Thanks @chris85. All works fine now. But is this enough to prevent sql injection? — diagold, Jul 26 '17 at 13:05
Yes, it doesn't put the user data directly in query so it can't be manipulated — chris85, Jul 26 '17 at 13:06

score 1 · Accepted Answer · answered Jul 26 '17 at 12:57

Though SQLite syntax is similar to PDO, you can't pass parameters in execute (see the manual - http://php.net/manual/en/sqlite3stmt.execute.php, no arguments to this function are available). So you need to use bindParam/bindValue.

Second, execute() method returns SQLiteResult, you should iterate over it, now over $stmt.

Third, SQLiteResult has no fetch method, only fetchArray. And fourth - as you don't use PDO, PDO:: constants are useless.

$postid = (int) $_GET['post_id'];

$userpostquery = "SELECT * FROM  users WHERE userid = ?";
$stmt = $db->prepare($userpostquery);
// treat $postid as INTEGER
$stmt->bindParam(1, $postid, SQLITE3_INTEGER);
// get SQLiteResult
$result = $stmt->execute();

while ($row = $result->fetchArray(SQLITE3_ASSOC)) 
{
    $cname = $row['cname'];

    echo $cname;
}

Sql prepared statement execute() error

1 Answers1