错误检查PDO准备的语句 [英] Error Checking for PDO Prepared Statements

问题描述

我正在尝试使用PDO准备好的语句为MySQL数据库上的查询创建正确的错误处理.我希望程序在检测到预准备语句过程中的错误时退出.利用PDO准备语句过程中的每个步骤均在失败时返回False的事实，我将这种令人讨厌的hack归类到一起:

I'm trying to create proper error handling for queries on a MySQL database using PDO prepared statements. I want the program to exit the moment an error in the prepared statement process is detected. Taking advantage of the fact that each step in the PDO prepared statement process returns False on failure, I threw together this repugnant hack:

 global $allFields;
 global $db;
 global $app;
 //dynamically append all relevant fields to query using $allFields global
 $selectQuery = 'SELECT ' . implode($allFields, ', ') .
     ' FROM People WHERE ' . $fieldName . ' = :value';
 //prepared statement -- returns boolean false if failure running query; run success check
 $success = $selectQueryResult = $db->prepare($selectQuery);
     checkSuccess($success);
 $success = $selectQueryResult->bindParam(':value', $fieldValue, PDO::PARAM_STR);
     checkSuccess($success);
 $success = $selectQueryResult->execute();
     checkSuccess($success);

和checkSuccess()一起执行以下操作:

function checkSuccess($success) {
    if ($success == false) {
        //TODO: custom error page. 
        echo "Error connecting to database with this query.";
        die();
    }
}

两件事.首先，这是非常冗长而愚蠢的.肯定有更好的办法.显然，我可以将布尔值存储在数组或其他东西中，以取出一行或两行代码，但是仍然可以.

Two things. First, this is horribly verbose and stupid. There must be a better way. Obviously I could store the booleans in an array or something to take out a line or 2 of code, but still.

第二，甚至有必要检查这些值，还是应该在执行以下代码后检查结果:

Second, is it even necessary to check these values, or should I just check the result after I perform this line of code:

$result = $selectQueryResult->fetch(PDO::FETCH_ASSOC);

我已经有执行此操作的代码:

I already have code that does this:

if ($result) { //test if query generated results
    // do successful shit
}

else {
    echo "404";
    $app->response()->status(404); //create 404 response header if no results

尽管我尝试通过插入奇怪的，不匹配的或冗长的查询来中断准备好的语句过程，但我的程序始终将其分配为$result赋值，而不会在我运行false >.因此，也许我根本不需要检查以上逻辑?请记住，我在程序的前面检查了数据库连接是否成功.

As much as I try to break the prepared statement process by inserting weird, mismatched, or lengthy queries, my program always makes it to the $result assignment without returning false on any of the functions where I run checkSuccess(). So maybe I don't need to be checking the above logic at all? Keep in mind that I check for a successful database connection earlier in the program.

推荐答案

我倾向于将错误模式设置为引发如下异常:

I preffer setting the error mode to throwing exceptions like this:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

在我连接到数据库之后.所以每个问题都会抛出一个PDOException 因此您的代码将是:

right after I connect to the database. So every problem will throw an PDOException So your code would be:

$selectQuery = '
                SELECT 
                    ' . implode($allFields, ', ') . ' 
                FROM 
                    People 
                WHERE 
                    ' . $fieldName . ' = :value
';
try
{ 
    $selectQueryResult = $db->prepare($selectQuery);
    selectQueryResult->bindParam(':value', $fieldValue);
    $selectQueryResult->execute();
}
catch(PDOException $e)
{
    handle_sql_errors($selectQuery, $e->getMessage());
}

函数将在哪里

function handle_sql_errors($query, $error_message)
{
    echo '<pre>';
    echo $query;
    echo '</pre>';
    echo $error_message;
    die;
}

事实上，我使用的通用函数也具有类似的功能

In fact I am using a general function that also has something like

$debug = debug_backtrace();
echo 'Found in ' . $debug[0]['file'] . ' on line ' . $debug[0]['line'];

告诉我如果我正在运行多个查询，问题出在哪里

to tell me where was the problem if I am running multiple queries

这篇关于错误检查PDO准备的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！