php - mysqli_real_escape_string() 无法正常运行

Question

我有以下 PHP 函数:

public function signup() {        $mysql = mysqli_connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);        if (mysqli_connect_errno($mysql)) {            $this->viewModel->set("pageTitle", "Signup");            $this->viewModel->set("message", "There was an error connecting to the server.");            return $this->viewModel;        }        if ($result = $mysql->query("SELECT id FROM mailinglist WHERE email='" . $this->email . "';")) {            if ($result->num_rows == 0) {                $mysql->query("INSERT INTO mailinglist (email) VALUES ('" . $this->email . "');");                $this->viewModel->set("message", "Great! Thanks for signing up " . $this->email . ".");            } else {                $this->viewModel->set("message", "You are already signed up for updates!");            }        } else {            $this->viewModel->set("message", "There was an error adding you the mailing list.");        }        $this->viewModel->set("pageTitle", "Signup");        return $this->viewModel;    }

它运行良好并且返回的正是我想要的，但是，如果我尝试在查询中使用 mysqli_real_escape_string() ，它不起作用。也就是下面的代码

public function signup() {        $mysql = mysqli_connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);        if (mysqli_connect_errno($mysql)) {            $this->viewModel->set("pageTitle", "Signup");            $this->viewModel->set("message", "There was an error connecting to the server.");            return $this->viewModel;        }        $query = $mysql->real_escape_string("SELECT id FROM mailinglist WHERE email='" . $this->email . "';");        if ($result = $mysql->query($query)) {            if ($result->num_rows == 0) {                $query = $mysql->real_escape_string("INSERT INTO mailinglist (email) VALUES ('" . $this->email . "');");                $mysql->query($query);                $this->viewModel->set("message", "Great! Thanks for signing up " . $this->email . ".");            } else {                $this->viewModel->set("message", "You are already signed up for updates!");            }        } else {            $this->viewModel->set("message", "There was an error adding you the mailing list.");        }        $this->viewModel->set("pageTitle", "Signup");        return $this->viewModel;    }

不起作用。这不是连接问题，我尝试使用 mysqli_real_escape_string() 而不是 $mysql->real_escape_string() 但它们都不起作用。谁能看出这段代码有什么问题吗？

Answer 1

不要这样做，使用准备好的语句。它们更安全、更可靠。您仍然需要清理数据以获得适当的值和跨站点脚本编写，仅举几个您仍然会遇到的风险。转义数据是防止 SQL 注入(inject)的一种方法，但这并不能完全证明。准备好的语句告诉数据库服务器假设传入的数据不安全并只是接受它，并且不要像连接字符串一样处理它。数据库将您的参数视为语句的变量而不是语句的一部分。

以下是如何将您的声明更改为准备好的声明:

$stmt=$mysql->prepare("SELECT id FROM mailinglist WHERE email=?");
$stmt->bind_param('s',$this->email);
$result=$stmt->execute();
if ($result) {
    if ($result->num_rows == 0) {
        $stmt=$mysql->prepare("INSERT INTO mailinglist (email) VALUES (?)");
        $stmt->bind_param('s', $this->email);
        $stmt->execute();
        $this->viewModel->set("message", "Great! Thanks for signing up " . $this->email . ".");
     } else {
        $this->viewModel->set("message", "You are already signed up for updates!");
     }
} else {
     $this->viewModel->set("message", "There was an error adding you the mailing list.");

}