php - lastinsertId()在与exec()一起使用时不能与execute()一起使用？

这是我完整的PHP代码，除了仅包含服务器信息的配置文件。此代码通过执行准备操作时的执行错误来防止sql注入，并且不返回lastinserted id。是否有任何解决方案通过准备语句并同时返回最后插入的ID来防止SQL注入。

<?php
$root = dirname(dirname(__FILE__));
require_once "$root/core/config/config.php";
class engine {
//constructing database connection based on configuration parameters
function __construct() {

    if(DB_SERVER!="" && DB_USERNAME!="" && DB_PASSWORD!="" && DB_NAME!="") {
        try {
            $this->conn = new PDO('mysql:host='.DB_SERVER.'; dbname='.DB_NAME, DB_USERNAME, DB_PASSWORD);
            //$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); PDO::ERRMODE_EXCEPTION for testing db errors
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }
    } else {
        $url = pathinfo($_SERVER["PHP_SELF"], PATHINFO_DIRNAME) . "/" . $path;
        $url = preg_replace("/\/\/+/", "/", $url);
        $url = "http://" . $_SERVER["SERVER_NAME"] . $url;
        die("no database login");
    }
}

在这里发生问题，如果我在执行前准备语句，则不会返回lastinsertid。通过错误lastinsertid方法错误

//function to insert data in given table
public function insert($postcol, $postval, $table){
    $sql="INSERT INTO ".$table;
    $sql.=" ".$postcol;
    $sql.=" VALUES ".$postval;
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
    $lastid = $stmt->lastInsertId();
    return($lastid);
}

如果上述功能更改为直接执行，则可以按以下方式正常工作

//function to insert data in given table
public function insert($postcol, $postval, $table){
    $sql="INSERT INTO ".$table;
    $sql.=" ".$postcol;
    $sql.=" VALUES ".$postval;
    $stmt = $this->conn->exec($sql);
    $lastid = $stmt->lastInsertId();
    return($lastid);
}

下面的代码是剥离输入并转发数据以插入功能

public function formpro($post,$table,$action){
    switch($action){
        case "insert" :
        $numItems = count($post);
        $i = 0;
        $col="(";
        $val="(";
        foreach($post as $key=>$value) {
          if(++$i != $numItems) {
            $col.="`".$key."`, ";
            if(is_numeric($val)){
                $val.=$value.", ";
            }
            else{
            $val.="'".$value."', ";
                }
          }
          else{
            $col.="`".$key."`";
             if(is_numeric($val)){
                $val.=$value;
            }
            else{
            $val.="'".$value."'";
                }
          }
        }
        $col.=")";
        $val.=")";
        return(engine::insert($col,$val,$table));
    }
}
}
//to check class worked following data processed
$arr=array("user_name"=>"addya", "full_name"=>"ananda bhat", "password"=>"fattos", "rank"=>"3");
$engine=new engine();
echo($engine->formpro($arr,"users","insert"));

最佳答案

lastInsertId()不是PDOStatement的方法，而只是PDO的方法。因此，即使使用语句执行查询，您仍然必须在连接上调用lastInsertId()。

public function insert($postcol, $postval, $table){
    $sql="INSERT INTO ".$table;
    $sql.=" ".$postcol;
    $sql.=" VALUES ".$postval;
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
    $lastid = $this->conn->lastInsertId();
    return($lastid);
}