Asked  7 Months ago    Answers:  5   Viewed   208 times

Possible Duplicate:
PDO Invalid parameter number - parameters in comments

Today I encountered a bug (in PDO) I never saw before, but is kinda obvious when you think about it.

I got the following error: Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

The query I was using was similar to the following:

SELECT
    x
FROM
    y
WHERE
    -- CHECKING IF X = ? --
    x = :y
AND
    1 = 2

Obviously I had more parameters and a longer query.

Why does it give me this error?

 Answers

21

The solution is obvious: PDO disregards comments as such and sees the ? as a positional parameter. Removing the ? in your comment solves this problem.

There's a similar bug using unbound parameters in comments.

Saturday, May 29, 2021
 
Gigamegs
answered 7 Months ago
95

You need

[PDO_SQLITE]
extension=pdo_sqlite.so

to be enabled, for sqlite:.subscribers.db

or, for windows:

[PHP_PDO_SQLITE]
extension=php_pdo_sqlite.dll

And ofcourse this extension in your ext directory

Wednesday, March 31, 2021
 
PeterTheLobster
answered 9 Months ago
12

PDOStatement::rowCount() returns

... the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Welcome to PDO, where the easy stuff works and the not-so-easy stuff ruins your day. SQLite is one of the drivers that doesn't have a reliable "how many rows are in my result set?" function. From the comments:

As of SQLite 3.x, the SQLite API itself changed and now all queries are implemented using "statements". Because of this, there is no way for PDO to know the rowCount of a SELECT result because the SQLite API itself doesn't offer this ability.

A return of false from PDOStatement::fetch() is a guarantee of "nothing came back," and your checking code is entirely sane, if a bit hard to read. You may wish to consider wrapping or deriving from PDO and PDOStatement for your own sanity.

(Disclaimer: I am a PDO fanboy.)

Wednesday, March 31, 2021
 
etsous
answered 9 Months ago
48

The solution is obvious: PDO disregards comments as such and tries to bind the non-existent variable ':Z'. You can't use parameters in comments in PDO (unless you do bind them).

There's a similar bug using question marks in comments.

Friday, May 28, 2021
 
Tucker
answered 7 Months ago
68

You mentioned two parameters (with the same name) for the prepare statement, yet you supply a value for the first parameter only (that's what the error was about).

Not quite sure how PDO internally solved the same-parameter-name issue, but you can always avoid that.

Two possible solutions:

$sql = "select * from $table ".
       "where "
       "first_name like concat('%', :fname, '%') or ".
       "last_name  like concat('%', :lname, '%')";
$stmt= $DBH->prepare($sql);
$stmt->bindValue(':fname', $string, PDO::PARAM_STR);
$stmt->bindValue(':lname', $string, PDO::PARAM_STR);

$sql = "select * from $table ".
       "where "
       "first_name like concat('%', ?, '%') or ".
       "last_name  like concat('%', ?, '%')";
$stmt= $DBH->prepare($sql);
$stmt->bindValue(1, $string, PDO::PARAM_STR);
$stmt->bindValue(2, $string, PDO::PARAM_STR);

By the way, the existing way you have done still has SQL injection issues.

Friday, July 30, 2021
 
jul
answered 4 Months ago
jul
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share