douluchuo0801
2018-05-31 05:41
浏览 103
已采纳

PHP pdo:使用参数两次时参数号无效?

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>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douba9020 2018-05-31 06:48
    已采纳

    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.

    点赞 评论
  • doupan6648 2018-05-31 05:57

    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); 
    
    点赞 评论
  • dongxianshuai8927 2018-05-31 06:33

    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"];
    
    点赞 评论

相关推荐 更多相似问题