PHP | SQL - mysqli_stmt_prepare 失败并连接到数据库

Question

我正在尝试执行参数化查询来更新数据库中的一些内容。

问题是 mysqli_stmt_prepare 失败了。require 用于连接数据库。

require 'includes/dbInclude.php';
if ($codeQuery > 0){
    $confirmationUsername = $_GET['confirmationUsername'];
    $active = "active";
    $noCode = "";
    $insertSql = "UPDATE users SET accountStatus = ? WHERE username = $confirmationUsername";
    $insertSql2 = "UPDATE users SET confirmationCode = ? WHERE username = $confirmationUsername";
    $statement = mysqli_stmt_init($connection);
    $statement2 = mysqli_stmt_init($connection);
    if (!mysqli_stmt_prepare($statement, $insertSql)){
        header("Location: registerComplete.php?error=sqlError1");
        exit();
    }
    elseif (!mysqli_stmt_prepare($statement2, $insertSql2)){
        header("Location: registerComplete.php?error=sqlError2");
        exit();
    }
    else{
        mysqli_stmt_bind_param($statement, "s", $active);
        mysqli_stmt_execute($statement);
        mysqli_stmt_bind_param($statement2, "s", $noCode);
        mysqli_stmt_execute($statement2);
    }
}

dbInclude.php 包含:

<?php

//connection variables
$serverName = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "ecglive";

//connection
$connection = mysqli_connect($serverName, $dbUsername, $dbPassword, $dbName);

//connection error
if(!$connection){
    die("There was an error connceting to the database: " . mysqli_connect_error());
}

在我使用它的地方有效。我还尝试将该代码复制到此代码，只是为了查看连接到数据库时是否有任何问题。它不是。

它总是会出现第一个错误，如果它说 sqlError1，如果我删除它，那么它会转到 sqlError2。

我做错了吗？

Answer 1

除了 accountstatus 之外，您还需要绑定(bind) username 以帮助缓解 SQL 注入(inject)。

require 'includes/dbInclude.php';

if ($codeQuery > 0){

    $confirmationUsername = $_GET['confirmationUsername'];
    $active = "active";
    $noCode = "";

    $insertSql = "UPDATE users SET accountStatus = ? WHERE username = ?";
    $insertSql2 = "UPDATE users SET confirmationCode = ? WHERE username = ?";

    $statement = mysqli_stmt_init($connection);
    $statement2 = mysqli_stmt_init($connection);


    if (!mysqli_stmt_prepare($statement, $insertSql)){
        exit(header("Location: registerComplete.php?error=sqlError1") );
    } elseif (!mysqli_stmt_prepare($statement2, $insertSql2)){
        exit(header("Location: registerComplete.php?error=sqlError2") );
    } else{

        mysqli_stmt_bind_param($statement, "ss", $active,$confirmationUsername);
        mysqli_stmt_execute($statement);

        mysqli_stmt_bind_param($statement2, "ss", $noCode,$confirmationUsername);
        mysqli_stmt_execute($statement2);
    }
}