PHP/MySQLi:如何防止插入 SQL 注入(代码部分工作) [英] PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)

问题描述

我我想使用 PHP 在 MySQL 数据库中存储两个值(一个电子邮件和一个密码).输入通过 jQuery 中的 Ajax 传递到 PHP 页面(通过网站上的 onclick 事件).

II would like to store two values (an email and a password) in a MySQL db using PHP. The input is passed to the PHP page via Ajax in jQuery (through an onclick event on a website).

现在我想保护这个 PHP 查询免受 SQL 注入 - 我说的是一个标准的网站，所以我不想过度，但我认为一些标准的保护不会坏.

Now I want to protect this PHP query against SQL injection - I am talking about a standard website so I don't want to overdo it but I think some standard protection can't be bad.

以下是我之前所做的，只是为了确保一般程序正常工作.然后我尝试通过使用密码加密和语句来提高安全性，但我不确定我是否以正确的方式这样做+我不知道这是否以及如何需要也适用于选择部分(我正在检查电子邮件是否已存在).

Below is what I had before just to ensure that the general procedure is working which it is. I then tried to increase security by using password encryption and statements but I am not sure if I did this the right way + I don't know if and how this needs to be applied to the Select part as well (where I am checking if the email already exists).

我已经看过很多关于这个主题的页面，但作为初学者，这正是我的问题所在.有人可以以此为例说明应该在此处更改或添加哪些内容，并可能提供一些简短的解释吗?如果可能，我想继续使用 MySQLi.

I've seen plenty of pages on this topic but as a beginner that's exactly my problem here. Can someone tell with this as an example what should be changed or added here and maybe provide some short explanations ? I would like to keep using MySQLi if possible.

旧 PHP:

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$pw = $_POST["pw"]; 

$sql = "SELECT email FROM Users WHERE email = '" . $email . "'";
$query = $conn->query($sql);
if(mysqli_num_rows($query) > 0){
    echo "Record already exists";
}else{
    $sql = "INSERT INTO Users (email, pw) VALUES ('" . $email . "', '" . $pw . "')";
    if($conn->query($sql)){
        echo "Update successful";
    }else{
        echo "Update failed";
    };
}
$conn->close();

新 PHP:

$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$email = $_POST["email"];
$pw = password_hash($_POST["pw"], PASSWORD_BCRYPT); 

$sql = "SELECT email FROM Users WHERE email = '" . $email . "'";
$query = $conn->query($sql);
if(mysqli_num_rows($query) > 0){
    echo "Record already exists";
}else{
    $sql = $conn->prepare("INSERT INTO Users (email, pw) VALUES ('" . $email . "', '" . $pw . "')");
    $sql->bind_param('s', $name);
    $sql->execute();
    $result = $sql->get_result();
    if($result){
        echo "Update successful";
    }else{
        echo "Update failed";
    };
}
$conn->close();

推荐答案

在 新 PHP 代码 片段中，您仍然容易受到注入攻击.
您在插入部分使用了准备好的语句，但您实际上没有正确使用准备强度.

In the New PHP code snippet, you are still vulnerable to injections.
You are using a prepared statement in the insert part, but you are not actually using the preparations strengths correctly.

在创建准备好的语句时，您将创建一个查询，在其中添加占位符而不是原始值:

When creating a prepared statement, you create a query in which you add placeholders instead of the raw values:

$stmt = $conn->prepare("INSERT INTO Users (email, pw) VALUES (?, ?)");

问号是占位符，稍后使用 bind_param 方法替换:

The question marks are the placeholders and are later replaced by using the bind_param method:

$stmt->bind_param('ss', $email, $pw);

绑定调用的 ss 部分告诉 mysql 数据库它的两个字符串被传递到数据库(s 表示 string，i 用于 int 等).
您正在绑定一个参数 ($name) 但它在查询中没有占位符或任何类型的引用..?

The ss part of the bind call tells the mysql db that its two strings that are passed to the database (s for string, i for int etc).
You are binding a param ($name) but it has no placeholder nor any type of reference in the query..?

另一方面，您的 select 语句仍然不安全并且容易出现漏洞.
我可能会在那里使用准备好的语句，就像插入部分一样.

Your select statement on the other hand is still unsafe and open to vulnerabilities.
I would probably use a prepared statement there to, just as with the insert part.

您总是希望确保用户的输入对数据库是安全的"，如果您连接查询字符串并将用户输入添加到其中，数据库将不会对字符串进行转义，它只会运行它.

You always want to make sure that input from the user is "safe" for the database, if you concat a query string and add user input into it, the database will not escape the strings, it will just run it.

当你自己编写完整的查询时，只使用标准的query方法调用，没有任何输入参数，尤其是没有用户传递的输入参数！

Only use standard query method calls when you write the full query yourself, without any input params, and especially no input params that the user passed!

这篇关于PHP/MySQLi:如何防止插入 SQL 注入(代码部分工作)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！