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

username除了以外，您还需要绑定，accountstatus以帮助减轻SQL注入。require 'includes/dbInclude.php';if ($codeQuery > 0){&nbsp; &nbsp; $confirmationUsername = $_GET['confirmationUsername'];&nbsp; &nbsp; $active = "active";&nbsp; &nbsp; $noCode = "";&nbsp; &nbsp; $insertSql = "UPDATE users SET accountStatus = ? WHERE username = ?";&nbsp; &nbsp; $insertSql2 = "UPDATE users SET confirmationCode = ? WHERE username = ?";&nbsp; &nbsp; $statement = mysqli_stmt_init($connection);&nbsp; &nbsp; $statement2 = mysqli_stmt_init($connection);&nbsp; &nbsp; if (!mysqli_stmt_prepare($statement, $insertSql)){&nbsp; &nbsp; &nbsp; &nbsp; exit(header("Location: registerComplete.php?error=sqlError1") );&nbsp; &nbsp; } elseif (!mysqli_stmt_prepare($statement2, $insertSql2)){&nbsp; &nbsp; &nbsp; &nbsp; exit(header("Location: registerComplete.php?error=sqlError2") );&nbsp; &nbsp; } else{&nbsp; &nbsp; &nbsp; &nbsp; mysqli_stmt_bind_param($statement, "ss", $active,$confirmationUsername);&nbsp; &nbsp; &nbsp; &nbsp; mysqli_stmt_execute($statement);&nbsp; &nbsp; &nbsp; &nbsp; mysqli_stmt_bind_param($statement2, "ss", $noCode,$confirmationUsername);&nbsp; &nbsp; &nbsp; &nbsp; mysqli_stmt_execute($statement2);&nbsp; &nbsp; }}

Answer 2

这段代码使用了一种非常奇怪的样式，它的冗长程度远远超过了必需的样式。这是相同形式的更简单形式：require 'includes/dbInclude.php';// Enable exception reportingmysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);if ($codeQuery > 0) {&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; // Prepare one query that sets both properties.&nbsp; &nbsp; &nbsp; $stmt = $connection->prepare('UPDATE users SET accountStatus=?,confirmationCode=? WHERE username=?');&nbsp; &nbsp; &nbsp; // Bind parameters directly form the source, no variables needed.&nbsp; &nbsp; &nbsp; $stmt->bind_param('ss', 'active', '', $_GET['confirmationUsername']);&nbsp; &nbsp; &nbsp; // Attempt to execute&nbsp; &nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; }&nbsp; &nbsp; catch (Exception $e) {&nbsp; &nbsp; &nbsp; // Error handling here...&nbsp; &nbsp; &nbsp; header("Location: registerComplete.php?error=sqlError2");&nbsp; &nbsp; &nbsp; exit();&nbsp; &nbsp; }}您在这里实际上并没有做很多事情，因此没有理由使代码如此冗长。就是说，如果这是用于某种用户访问控制层的注册系统，并且这不是一个学术项目，则应在创建巨大混乱之前停止使用此代码。编写自己的访问控制层并不容易，并且有很多机会可以使它严重错误。诸如Laravel这样的任何现代开发框架都内置了强大的身份验证系统。这是一个已解决的问题，您无需尝试在这里重新发明轮子。至少应遵循建议的最佳安全最佳做法，并且永远不要将密码存储为纯文本或弱哈希（如SHA1或MD5）。