Mysqli准备的语句(防止SQL注入) [英] Mysqli prepared statement (SQL injection prevention)

问题描述

在停止使用不推荐使用的mysql_ *函数之后，我切换到mysqli.但是后来，我注意到未准备好的语句对于SQL注入是不安全的.然后，我再次更改了代码.

after stopping the use of deprecated mysql_* functions, I switched to mysqli. But then, I noticed that non-prepared statements are non-secure against SQL injection. Then, I changed again my code.

我所拥有的是以下函数，该函数检查数据库中是否存在变量 $ ID 并为该行打印 title 的值:

What I had was the following function that checks if the variable $ID exists in the database and prints the value of title for that row:

function showPostTitle($ID, $mysqli) {
    $result = $mysqli -> query("SELECT ID, title FROM blog where ID = $ID");
    $row = $result -> fetch_array(MYSQLI_BOTH);
    echo $row['title'];
}

我将其更改为:

function showPostTitle($ID, $mysqli) {
    $stmt = $mysqli -> prepare("SELECT ID, title FROM blog WHERE ID = ?");
    $stmt -> bind_param("i", $ID);
    $stmt -> execute();
    $stmt -> bind_result($ID, $title);
    $stmt -> fetch();
    print_r($title);
    $stmt -> free_result();
}

我的问题是:这是执行准备好的语句的正确方法吗?另外，我现在可以安全使用SQL注入吗?非常感谢任何愿意回答这个问题的人:)

My question is: is this the correct way to implement prepared statements? Plus, am I safe now from SQL Injections? Big thanks to whoever will answer this question :)

推荐答案

您的mysqli逻辑看起来不错，在

Your mysqli logic seems fine, there are some examples in the PHP manual here in case you have not seen them.

为什么不使用ID时为什么要选择ID?另外，当只需要在完整结果集中返回一行时，您实际上就不需要绑定结果，因为我认为在这种情况下会发生这种情况(ID是表中的唯一索引)，请使用

Why are you selecting the ID when not consuming it though? Also you don't really need to bind a result when it's only going to have one row returned in the full result set as I assume will happen in this case (ID is unique index in the table), use get_result instead.

使用mysqli prepare可以抵御所有常见的注入攻击，但不能抵御0-day风格的东西，而这种风格尚未出现在驱动程序中.

Using mysqli prepare will protect against all the common injection attacks but not 0-day style stuff which hasn't made it to the driver yet.

这篇关于Mysqli准备的语句(防止SQL注入)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！