在PHP中将MySQL参数化为函数 - Parameterised MySQL in PHP as a function

我正在着手完全重写我管理的旧站点，并利用这个机会尽可能多地进行最佳实践/整理代码。考虑到这一点，我热衷于将数据库调用从页面呈现代码中移出，并移动到我可以重用的常用函数库中 - 如果您愿意，可以将其作为准 MVC 模型。但是，重写的目标之一是尽可能保持安全性，我怀疑实现这一目标的最佳方法是使用参数化/参数化查询。

因此，假设我的代码想要返回的通常是一个记录集数组，有没有办法编写一个函数来足够灵活地处理各种传入的 SQL 查询，但仍然可以参数化？

你应该使用 PDO。

进行参数化查询：

$prep = $db->prepare("SELECT * FROM `users` WHERE userid = :id");
$prep->execute(array(":id" => $userid);

它处理所有类型的查询（插入、选择、更新语句，甚至存储过程调用）。看看这个页面

使用我写的这个类。它的帮助

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();
    }
}

}