php - 我正在努力使用 PDO 升级登录脚本

Question

我的函数中有一个自定义 query() 函数。文件。我创建了一个 login.php 文件，但是当我进行 SQL 查询时，query() 函数返回一个 PDO 对象，而不是我想要的关联数组。我需要帮助来传递要绑定(bind)到存储过程/准备好的语句的参数。

以下是login.php文件:

<?php

    // configuration
    require("../../includes/config.php");

    // if form was submitted
    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {
        // validate submission
        if (empty($_POST["username"]))
        {
            adminapologize("You must provide your username.");
        }
        else if (empty($_POST["password"]))
        {
           adminapologize("You must provide your password.");
        }

        $username = $_POST["username"];

        // query database for user
        $sql = "SELECT * FROM admin WHERE username = '$username'";

        $result = query($sql,array($username));        
        //var_dump($result);
        //exit;

        if($sql != false)
        {
            if($result->rowCount() == 0)
            {
                printf("No admin yet.");
            }

            // if we found user, check password
            if($result->rowCount() == 1)
            {
                // first (and only) row
                $row = $result->fetch();

                // compare hash of user's input against hash that's in database
                if ($_POST["username"] == $row["username"] && crypt($_POST["password"], $row["hash"]) == $row["hash"])                 
                {
                    // remember that user is now logged in by storing user's ID in session
                    $_SESSION["admin_id"] = $row["admin_id"];

                    // redirect to admin home
                    redirect("index.php");
                }
            }
        }
        else
        {
            // else apologize
            adminapologize("Invalid username and/or password.");
        }
    }
    else
    {
        // else render form
        adminrender("login_form.php", ["title" => "Admin Log In"]);
    }

?>

请注意，config.php 包含functions.php 文件。以下是functions.php 文件的部分:

/**
* Executes SQL statement, possibly with parameters, returning
* a pdo statement object on success, handling and halting execution on error.
*/

function query($sql, $parameters = null)
{
    static $pdo; // define the var as static so that it will persist between function calls
    try
    {
        // if no db connection, make one
        if (!isset($pdo))
        {
            // connect to database
            // you should set the character encoding for the connection
            $pdo = new PDO("mysql:dbname=" . DB_NAME . ";host=" . DB_SERVER, DB_USERNAME, DB_PASSWORD);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // turn emulated prepares off
            $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc so that you don't have to explicitly list the fetch mode every place
        }

        if(empty($parameters))
        {
            // no bound inputs
            $stmt = $pdo->query($sql);
        } else {
            // has bound inputs
            $stmt = $pdo->prepare($sql);
            // you should use explicit bindValue() statements to bind any inputs, instead of supplying them as a parameter to the ->execute() method. the comments posted in your thread lists the reasons why.
            $stmt->execute($parameters);
        }
    }
    catch (Exception $e)
    {
        // all errors with the connection, query, prepare, and execute will be handled here
        // you should also use the line, file, and backtrace information to produce a detailed error message
        // if the error is due to a query, you should also include the $sql statement as part of the error message
        // if $pdo ($handle in your code) is set, it means that the connection was successful and the error is due to a query. you can use this to include the $sql in the error message.
        trigger_error($e->getMessage(), E_USER_ERROR);

        //exit; // note: E_USER_ERROR causes an exit, so you don't need an exit; here.
    }

    return $stmt; // if the query ran without any errors, return the pdo statement object to the calling code

}

非常感谢您的帮助。

Answer 1

您的功能非常出色，无需剧透。

the query() function returns a PDO object, instead of an associative array I desire.

它实际上是一个你想要返回的对象。至于数组，您可以通过将 fetch 链接到调用来简单地获取它:

$result = query($sql,array($username))->fetch();   // voila!

看，使用返回对象的函数，您不仅可以获得单行，还可以获得数十种不同类型的结果。类似于 fetchColumn() 的单列值或 fetchAll() 支持的多种格式。更不用说你可以从对象中获取 numRows() 而从数组中则不能。

使用当前形式的此函数，您也可以运行 DML 查询，但返回 fetch 时会出现错误。返回一个对象是一件非常非常酷的事情!

您的函数唯一的缺点是您正在捕获异常并将其手动转换为错误，而 PHP 已经为您做这件事了。
只要去掉这个 try catch block ，你就会得到完全相同的(实际上，甚至更好)的错误报告。

    // all errors with the connection, query, prepare, and execute will be handled here
    // you should also use the line, file, and backtrace information to produce a detailed error message
    // if the error is due to a query, you should also include the $sql statement as part of the error message
    // if $pdo ($handle in your code) is set, it means that the connection was successful and the error is due to a query. you can use this to include the $sql in the error message.

如果您不捕获异常，PHP 已经为您做了所有这些事情。

(保存实际不需要的 $sql 变量，因为您可以在回溯跟踪后找到查询)

就代码而言，它应该短五倍:

    $sql = "SELECT * FROM admin WHERE username = ?";
    $row = query($sql,array($username))->fetch();
    if($row && crypt($_POST["password"], $row["hash"]) == $row["hash"])
    {
         // remember that user is now logged in by storing user's ID in session
         $_SESSION["admin_id"] = $row["admin_id"];

         // redirect to admin home
         redirect("index.php");

         //this is essential as otherwise anyone will be able to proceed with this page
         exit;
    }

顺便说一句，我刚刚注意到您使用了错误的函数，将 $username 直接发送到查询中。我也修好了。