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

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (44)

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

问题是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。

我犯了什么错吗?

提问于
用户回答回答于

您需要绑定username

除了accountstatus以帮助缓解SQL注入。

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);
    }
}
用户回答回答于

这段代码使用了一种非常奇怪的风格,而且这种风格比必要的更冗长。这是一个更小的形式:

require 'includes/dbInclude.php';

// Enable exception reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if ($codeQuery > 0) {
    try {
      // Prepare one query that sets both properties.
      $stmt = $connection->prepare('UPDATE users SET accountStatus=?,confirmationCode=? WHERE username=?');

      // Bind parameters directly form the source, no variables needed.
      $stmt->bind_param('ss', 'active', '', $_GET['confirmationUsername']);

      // Attempt to execute
      $stmt->execute();
    }
    catch (Exception $e) {
      // Error handling here...
      header("Location: registerComplete.php?error=sqlError2");
      exit();
    }
}

你真的没有在这里做很多事情,所以没有理由让这些代码如此冗长。

话虽这么说,如果这是某种用户访问控制层的注册系统,这不是一个学术项目,你应该在创建一个巨大的混乱之前停止处理这个代码。编写自己的访问控制层并不容易,并且有很多机会让它严重错误。

任何像Laravel这样的现代开发框架都内置了强大的身份验证系统。这是一个已解决的问题,您无需在此尝试重新发明轮子。

至少遵循建议的安全最佳实践绝不将密码存储为纯文本或弱哈希,如SHA1或MD5

扫码关注云+社区

领取腾讯云代金券