PHP pdo: Invalid Parameter Number when using a parameter twice?

Question

I have the Problem, that I can not use an SQL-Statement in PDO (PHP/mysql), when I use a positional parameter twice:

SELECT `ID` FROM `_LOGIN_` WHERE `LoginName` = :loginName AND `sha512`= SHA2(CONCAT(:pw, (SELECT `salt` FROM `_LOGIN_` WHERE `LoginName` = :loginName)), 512)

As you can see, I use ":loginName" twice. So the following error message appears:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number in ...

Am I missing something or is there another way to prepare the statement, so I can use parameters multiple times?

Here follows the complete code to reproduce:

<!DOCTYPE html>
<html>
  <head>
    <title>pdo</title>   

    <meta   charset = "utf-8" />
  </head>

  <body>
      <h1>PDO Prepare</h1>

      <!--
      DB:

DROP   DATABASE IF EXISTS `pdoTestDB`;
CREATE DATABASE           `pdoTestDB`;
ALTER  DATABASE           `pdoTestDB` DEFAULT CHARACTER SET 'utf8' DEFAULT COLLATE 'utf8_general_ci';

CREATE TABLE `_LOGIN_` (
 `ID`           int(11)      NOT NULL
,`LoginName`    TEXT         NOT NULL 
,`SALT`         varchar(  6) NOT NULL 
,`sha512`       varchar(128) NOT NULL 
,`registerTS`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP 
,`lastLoginTS`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP 
, PRIMARY KEY (`ID`)
) ;

SELECT @SALT:=SUBSTRING(MD5(RAND()) FROM 1 FOR 6);

INSERT INTO `_LOGIN_`
(`ID`, `LoginName`, `salt`, `sha512`                                     , `registerTS`         ) VALUES
(   1, 'muma'      , @SALT, SHA2(CONCAT('123', @SALT), 512), '2018-06-04'         );
      -->

<?php
$PDOcharset = 'utf8mb4';
// set data source name:
$dsn = "mysql:host=localhost;dbname=pdoTestDB;charset=$PDOcharset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, "santisPHP", "123", $opt);

// the sql
$sql = "SELECT `ID` FROM `_LOGIN_` WHERE `LoginName` = :loginName AND `sha512`= SHA2(CONCAT(:pw, (SELECT `salt` FROM `_LOGIN_` WHERE `LoginName` = :loginName)), 512)";

$stmt = $pdo->prepare($sql);

$paramsAssoc = ['loginName' => "muma", 'pw' => "123"];
        //echo $paramsAssoc;
var_dump($stmt);
var_dump($paramsAssoc);

$result = $stmt->execute($paramsAssoc);

$fetched = $stmt->fetch();

echo "stmt: ";
var_dump($stmt);
echo "<br />";

echo "result: ";
var_dump($result);
echo "<br />";

echo "fetched: ";
var_dump($fetched);
echo "<br />";
?>

  </body>

</html>

score 6 · Answer 1 · edited Jun 07 '21 at 11:30

PDO does not let you use the same parameter identifier more than once per query (unless you set the ATTR_EMULATE_PREPARES option to true as suggested by tttapa's answer). You need to change the identifier's name in the query and then then add another matching one in the params.

Like so:

// the sql
$sql = "SELECT `ID` FROM `_LOGIN_` WHERE `LoginName` = :loginName1 AND `sha512`= SHA2(CONCAT(:pw, (SELECT `salt` FROM `_LOGIN_` WHERE `LoginName` = :loginName2)), 512)";

$stmt = $pdo->prepare($sql);

$loginName = 'muma';
$pw = '123';

$stmt->bindParam(":loginName1",     $loginName);
$stmt->bindParam(":loginName2",     $loginName);
$stmt->bindParam(":pw",             $pw);

//echo $paramsAssoc;
var_dump($stmt);
//var_dump($paramsAssoc);

$result = $stmt->execute();

$fetched = $stmt->fetch();

I prefer binding my parameters like I have shown above, however using your method I believe you could also do this - same principal:

$paramsAssoc = ['loginName1' => "muma", 'pw' => "123", 'loginName2' => "muma"];
$result = $stmt->execute($paramsAssoc);

This is certainly a workaround one could use, but the issue is not a limitation of PDO - PDO actually supports a way to get around the issue, as explained in tttapa's answer. I took the liberty to edit the answer to point that out... — rob74, Jun 07 '21 at 11:47
@rob74: You are correct and thank you for taking the time to edit my answer. — Joseph_J, Jun 10 '21 at 06:31

AndreCunha · Answer 2 · 2018-05-31T06:40:15.743

As per low_rents's answer on a similar question, in your case you could do the following:

First, before your query, execute an additional query to define loginName as an User-Defined Variable:

$stmt = $pdo->prepare("SET @loginName = :loginName");
$stmt->bindValue(":loginName", "muma", PDO::PARAM_STR);
$stmt->execute();

Then, in your query, replace all :loginName instances with the now defined @loginName:

// the sql
$sql = "SELECT `ID` FROM `_LOGIN_` WHERE `LoginName` = @loginName AND `sha512`= SHA2(CONCAT(:pw, (SELECT `salt` FROM `_LOGIN_` WHERE `LoginName` = @loginName)), 512)";

Finally, execute it just like you were doing, but this time your $paramsAssoc array does not need the 'loginName' => "muma" since it is already defined within your MySQL instance and in your query, thus becoming just:

$paramsAssoc = ['pw' => "123"];

Good Idea, I did not think of this. But because all my SQL-Queries are stored in the database itselfs, It makes this a bit more difficult for me. But I agree, using User-Definde-Variables is a good workaround. — iGeeks, Jun 01 '18 at 14:03

score 0 · Accepted Answer · answered May 31 '18 at 06:48

0

Alternatively, you could change your settings to PDO::ATTR_EMULATE_PREPARES => true. This will allow you to bind the same named parameter multiple times by preparing the statements in PDO itself, rather than on the MySQL server.

answered May 31 '18 at 06:48

tttapa

1,397
12
26

I have just tried this and PDO::ATTR_EMULATE_PREPARES => false replaced by true. – iGeeks Jun 01 '18 at 14:19
I am not sure, which version (yours tttapa or Joseph_Js) is more perfomant, but for me, your version is easier to maintain, because I have all SQL-Statements already in the db. – iGeeks Jun 01 '18 at 14:20
I do not recommend changing `PDO::ATTR_EMULATE_PREPARES => true` as it will not actually parameterize your query, thus loosing your benefits that protect you from sql injection. – Joseph_J Jun 04 '18 at 11:10
1

@Joseph_J: that is not the case. Please read [this answer](https://stackoverflow.com/a/10455228/6356744) explaining the differences. _The same PDO code will be equally vulnerable (or not-vulnerable) to injection attacks regardless of your EMULATE_PREPARES setting_. – tttapa Jun 04 '18 at 12:10
1

When you emulate a prepare you are relying on PDO to properly escape your parameters. Which it does. When you use the native prepare(emulations set to false) the query is parsed by the sql engine before the parameters are ever introduced to the query. This means you do not need to worry about escaping the parameters because the structure of the query can not be changed. – Joseph_J Jun 05 '18 at 01:32
@Joseph_J: I don't really care if PDO or the SQL engine is doing the escaping, as long as it gets done. You might argue that escaping the parameters in PHP code is less performant, but I don't understand your comment that it doesn't protect you from SQL injection... – rob74 Jun 07 '21 at 11:35
@rob74: I do believe that you are missing the larger issue. With the emulation turned off, and a properly parameterized query, you do not need to escape your data at all. So the performance of PDO vs the SQL engine with regards to escaping is of zero concern to me. – Joseph_J Jun 10 '21 at 06:19

PHP pdo: Invalid Parameter Number when using a parameter twice?

3 Answers3