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>
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.
PDO does not let you use the same parameter identifier more than once per query. 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);
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"];