从mysqli转换为准备好的语句 [英] Converting from mysqli to prepared statements

问题描述

我正在尝试将代码从mysqli_ *语句转换为准备好的语句，以防止SQL注入.以下代码是我尝试转换的代码(目前可以正常运行):

I am attempting to convert code from the mysqli_* statements to prepared statements to prevent SQL injection. The following code is what I am attempting to convert (and it currently works correctly):

$details = mysqli_query($linkDB,"SELECT * FROM ".PREFIX."Issues WHERE id='".$_POST['article']."' AND disabled='0' LIMIT 1");
$detail = mysqli_fetch_assoc($details);

这是我尝试转换为准备好的语句的尝试.任何使它更简洁的方法将不胜感激(因为我要从两行代码变成许多行):

Here is my attempt at converting to prepared statments. Any way to make this more concise would be appreciated (since I'm going from 2 lines of code to many):

$SQL = "SELECT * FROM ".PREFIX."Issues WHERE id='?' AND disabled='0' LIMIT 1";
$PRE = mysqli_stmt_init($linkDB);
//if (! $PRE = mysqli_prepare($linkDB, $SQL)) {   (alt attempt)
    if (! mysqli_stmt_prepare($PRE, $SQL)) {
        echo "<f><msg>ERROR: Could not prepare query: ".$SQL.", ".mysqli_error($linkDB)."</msg></f>";
    } else {
        mysqli_stmt_bind_param($PRE, "i", $test);
        $test = $_POST['article'];
        if (! mysqli_stmt_execute($PRE)) {
            echo "<f><msg>ERROR: Could not execute query: ".$SQL.", ".mysqli_error($linkDB)."</msg></f>";
        } else{
            $details = mysqli_stmt_get_result($PRE);
            $detail = mysqli_fetch_assoc($details);
            mysqli_stmt_close($PRE);
        }
}

上面的代码不会在$ detail变量中返回/存储db值，以供以后在脚本中处理.我尝试注释掉mysqli_stmt_close($ PRE)调用，但这没什么区别.感谢您的帮助！

The above code does not return/store db values in the $detail variable for future processing later in the script. I have tried commenting out the mysqli_stmt_close($PRE) call, but that makes no difference. I appreciate your help!

推荐答案

代码中的主要错误是查询中带有'?'的错字.如果?在引号内，则不会将其视为占位符，而是视为文字值.

The main mistake in your code was the typo with '?' in your query. If the ? is inside quotes it is not treated as a placeholder, but as a literal value.

使用MySQLi时，您应该启用MySQLi异常模式.如果这样做，则不再需要检查每个函数的结果.您还应该使用OOP样式，因为它不太冗长，而且您犯傻错误的可能性也较小.

When using MySQLi you should enable MySQLi exception mode. If you do then there is no longer any need to check the result of each function. You should also use OOP style as it is less verbose and you are less likely to make a silly mistake.

// put this line before you open the MySQLi connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$stmt = $linkDB->prepare('SELECT * FROM '.PREFIX.'Issues WHERE id=? AND disabled=0 LIMIT 1');
$stmt->bind_param('i', $_POST['article']);
$stmt->execute();
$detail = $stmt->get_result()->fetch_assoc();

这篇关于从mysqli转换为准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！