php - PHP中的参数化MySQL功能

我正在着手重新编写一个我管理的旧站点，并利用这个机会尽可能多地进行代码的最佳实践/整理。考虑到这一点，我非常希望将数据库调用从页面呈现代码转移到一个可以重用的公共函数库中——如果您愿意的话，这是一个准MVC模型。然而，重新编写的目标之一是尽可能地保持安全性，我怀疑实现这一目标的最佳方法是使用参数化/参数化查询。
因此，假设我的代码想要返回的内容通常是一个记录集数组，那么有没有一种函数可以被编写成足够灵活，以处理各种传入的SQL查询，但仍然是参数化的？

最佳答案

用我写的这个班。它很有用

class Database {

public $hostname, $dbname, $username, $password, $conn;

function __construct() {
    $this->host_name = "HOST_NAME";
    $this->dbname = "DBNAME";
    $this->username = "USERNAME";
    $this->password = "PASSWORD";
    try {

        $this->conn = new PDO("mysql:host=$this->host_name;dbname=$this->dbname", $this->username, $this->password);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }
}

function customSelect($sql) {
    try {
         $stmt = $this->conn->prepare($sql);
        $result = $stmt->execute();
        $rows = $stmt->fetchAll(); // assuming $result == true
        return $rows;
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }
}

function select($tbl, $cond='') {
    $sql = "SELECT * FROM $tbl";
    if ($cond!='') {
        $sql .= " WHERE $cond ";
    }

    try {
         $stmt = $this->conn->prepare($sql);
        $result = $stmt->execute();
        $rows = $stmt->fetchAll(); // assuming $result == true
        return $rows;
    } catch (PDOException $e) {
        echo 'Error: ' . $e->getMessage();
    }
}
function num_rows($rows){
     $n = count($rows);
     return $n;
}

function delete($tbl, $cond='') {
    $sql = "DELETE FROM `$tbl`";
    if ($cond!='') {
        $sql .= " WHERE $cond ";
    }

    try {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount(); // 1
    } catch (PDOException $e) {
        return 'Error: ' . $e->getMessage();
    }
}

function insert($tbl, $arr) {
    $sql = "INSERT INTO $tbl (`";
    $key = array_keys($arr);
    $val = array_values($arr);
    $sql .= implode("`, `", $key);
    $sql .= "`) VALUES ('";
    $sql .= implode("', '", $val);
    $sql .= "')";

    $sql1="SELECT MAX( id ) FROM  `$tbl`";
    try {

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $stmt2 = $this->conn->prepare($sql1);
        $stmt2->execute();
        $rows = $stmt2->fetchAll(); // assuming $result == true
        return $rows[0][0];
    } catch (PDOException $e) {
        return 'Error: ' . $e->getMessage();
    }
}

function update($tbl, $arr, $cond) {
    $sql = "UPDATE `$tbl` SET ";
    $fld = array();
    foreach ($arr as $k => $v) {
        $fld[] = "`$k` = '$v'";
    }
    $sql .= implode(", ", $fld);
    $sql .= " WHERE " . $cond;

    try {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount(); // 1
    } catch (PDOException $e) {
        return 'Error: ' . $e->getMessage();
    }
}

}