php - 是否可以对具有串联变量的查询进行参数化？

Question

在学习 php 和 sql 注入(inject)时，我想参数化我的查询以获得安全可靠的网站应用程序。但是，我的不起作用我尝试参数化我的更新并选择我的查询，但我没有实现使程序正常工作的目标。

当前输出抛出错误，找不到？

到目前为止，这是我的代码，我是否遗漏了一些不起作用的东西？

<?php  
//connection
$connection = mysqli_connect("hostserver","username","");
$db = mysqli_select_db($connection, 'dbname');

if (isset($_POST['qrname'])) {
    $qrid = $_POST['qrid'];

    //Query No. 1
    $qrQuery = "SELECT * FROM scratch_cards WHERE code='$qrid' ";
    $qrQuery_run = mysqli_query($connection,$qrQuery);

    //Query No. 2
    $qrQuery2 = "UPDATE scratch_cards SET status = 'U' WHERE code='$qrid' ";
    $qrQuery_run2 = mysqli_query($connection,$qrQuery2);
    $qrQuery2->bind_param("s", $qrid);
    $qrQuery2->execute();

    while ($qrRow = mysqli_fetch_array($qrQuery_run)) {
        $txtQrvalue = $qrRow['amount'];
        $txtQrstatus = $qrRow['status'];

        // QUERY TO UPDATE THE VALUE
        // BIND AND PARAMETIZE MY QUERY
        $qrQuery3 = $db->parepare("UPDATE shopusers SET ewallet = ewallet + " . (0+?) . " WHERE id = '?' ");
        $qrQuery3->bind_param("ii", $txtQrvalue, $id);
        $qrQuery3->execute(); 
        //END
    }

Answer 1

如果我正确地阅读了您的问题和代码，您可以使用 JOIN 将其减少为两个查询，这样您就可以摆脱 SELECT陈述。对两者都使用准备好的语句。

我还将您的连接字符集指定为 UTF-8(您应该为 PHP 和 HTML header 以及数据库表进行设置)。

<?php 
$connection = mysqli_connect("hostserver","username","");
$db = mysqli_select_db($connection, 'dbname');
$connection->set_charset("utf8");

if (isset($_POST['qrname'])) {
    $qrid = $_POST['qrid'];

    $sql = "UPDATE scratch_cards SET status = 'U' WHERE code=?";
    $stmt = $connection->prepare($sql);
    $stmt->bind_param("s", $qrid);
    $stmt->execute();
    $stmt->close();

    $sql = "UPDATE shopusers su
               INNER JOIN scratch_cards sc
                 ON sc.qrid = su.code
               SET su.ewallet = su.ewallet + sc.amount,
                   sc.status = 'U'
               WHERE sc.code = ?";
    $stmt = $connection->prepare($sql);
    $stmt->bind_param("s", $qrid);
    $stmt->execute();
    $stmt->close();
}