是否可以参数化具有连接变量的查询？

Question

作为学习php和sql注入，我想参数化我的安全和安全的网站应用程序的查询。然而，我的不能工作，我试图参数化我的更新并选择我的查询，但我没有达到让程序工作的目标。

当前输出抛出错误:找不到?

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

<?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头文件以及数据库表设置该字符集)。

<?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();
}

Answer 2

我们在PDO bind param中有foll语法，我把你的update查询作为一个例子，它工作得很好。尝试搜索命名参数绑定

<?php


$user = 'root';
$pass = 'xxxx';
$DB = 'test';
$host = 'localhost';

$mysqlConnection = new \PDO('mysql:host='.$host.';dbname='.$DB, $user, $pass);
$mysqlConnection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$sql = 'update info set fname = fname + :fn where id = 1';

$stmt = $mysqlConnection->prepare($sql);
$stmt->bindValue(':fn', '100');
$stmt->execute();
echo $stmt->rowCount();


?>

Answer 3

这是您想要使用mysqli绑定参数运行的查询吗？

<?php

ini_set('display_errors', 1);
$user = 'root';
$pass = 'xxxx';
$DB = 'test';
$host = 'localhost';

$sql = 'update info set fname =  fname + ? where id = 1';

$conn = new mysqli($host, $user, $pass, $DB);
$stmt = $conn->prepare($sql);

$stmt->bind_param("i", $val);
$val = 100;
$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
exit;