更改一个简单的数据库类以支持准备好的语句

Question

我的问题是关于我的代码。我创建了 2 个网站。但它在 mysqli PHP OOP 上。有人告诉我，这些查询是 SQL 注入。所以，我想改变我的数据库结构来准备报表。然后，我可以从 SQL 注入中保存我的网站。为此，我需要你的帮助。 这是我的数据库结构：

config.php

<?php

define("DB_HOST", "localhost");

define("DB_USER", "root");

define("DB_PASS", "");

define("DB_NAME", "lunch");

?>

数据库.php

<?php

    $filepath = realpath(dirname(__FILE__));

    include_once ($filepath.'/../config/config.php');

?>

<?php

Class Database {

    public $host = DB_HOST;

    public $user = DB_USER;

    public $pass = DB_PASS;

    public $dbname = DB_NAME;

    public $link;

    public $error;

    public function __construct() {

        $this->connectDB();

    }

    private function connectDB() {

        $this->link = new mysqli($this->host, $this->user, $this->pass, $this->dbname);

        if (!$this->link) {

            $this->error = "Connection fail" . $this->link->connect_error;

            return false;

        }

    }

    public function select($query) {

        $result = $this->link->query($query) or

                die($this->link->error . __LINE__);

        if ($result->num_rows > 0) {

            return $result;

        } else {

            return false;

        }

    }

    public function insert($query) {

        $insert_row = $this->link->query($query) or

                die($this->link->error . __LINE__);

        if ($insert_row) {

            return $insert_row;

        } else {

            return false;

        }

    }

    public function update($query) {

        $update_row = $this->link->query($query) or

                die($this->link->error . __LINE__);

        if ($update_row) {

            return $update_row;

        } else {

            return false;

        }

    }

    public function delete($query) {

        $delete_row = $this->link->query($query) or

                die($this->link->error . __LINE__);

        if ($delete_row) {

            return $delete_row;

        } else {

            return false;

        }

    }

}

?>

因此，请检查我的数据库结构和查询，并告诉我如何更改我的数据库结构以准备语句以及如何防止 SQL 注入。请帮我。

Answer 1

我发现您课堂上的不同方法非常多余。他们都做同样的事情，而只是名称不同。所以首先让我们删除除一个方法之外的所有方法。我也会从课堂上删除一些其他货物崇拜代码。然后您将需要重写您的连接代码以添加错误报告和字符集支持。也添加define('DB_CHARSET', 'utf8mb4');到您的配置文件中。然后你必须重写这个类中剩下的唯一方法，以添加对准备好的语句的支持。有关详细信息，请参阅我的文章Mysqli 辅助函数。Class Database {&nbsp; &nbsp; public $link;&nbsp; &nbsp; public function __construct() {&nbsp; &nbsp; &nbsp; &nbsp; mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->link = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);&nbsp; &nbsp; &nbsp; &nbsp; mysqli_set_charset($this->link, DB_CHARSET);&nbsp; &nbsp; &nbsp; &nbsp; } catch (\mysqli_sql_exception $e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new \mysqli_sql_exception($e->getMessage(), $e->getCode());&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public function query($sql, $params = [], $types = "")&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; if ($params) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $types = $types ?: str_repeat("s", count($params));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt = $this->link->prepare($sql);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->bind_param($types, ...$params);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return $stmt->get_result();&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return $this->link->query($sql);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}现在，您可以使用准备好的语句将您的类与任何类型的查询一起使用$query = "INSERT INTO users(name) VALUES(?)";$this->db->query($query, [$data['name']]);header("Location: index.php");$query = "SELECT * FROM users WHERE user_id = ?";$result = $this->db->query($query, [$user_id]);$value = $result->fetch_assoc();$name = $value['name'];$this->db->query("DELETE FROM users WHERE user_id = ?",[$dlt_user]);header("location: rd-user.php");$query = "UPDATE users SET name = ? WHERE user_id = ?";$this->db->query($query,[$name,$userid]);header("Location: index.php");