Asked  7 Months ago    Answers:  5   Viewed   32 times

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);

with checkSuccess() doing the following:

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

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.

 Answers

28

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

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

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());
}

where the function would be:

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

Wednesday, March 31, 2021
 
Zach
answered 7 Months ago
44

Regarding to post LIMIT keyword on MySQL with prepared statement , the code below could solve my problem.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Thanks Álvaro G. Vicario and Maerlyn

Wednesday, March 31, 2021
 
ramdemon
answered 7 Months ago
19

Make sure you are putting quotes around the variable specified in AGAINST.

In PHP:

$some_term = '"'.$some_term.'"'; // Adds quotes around string

$stmt = $db->prepare('SELECT * FROM example WHERE MATCH(some_column) AGAINST(:some_term)');
$stmt->bindParam(':some_term', $some_term, PDO::PARAM_STR);
$stmt->execute();

Or you could do it in the MySQL statement as well:

$stmt = $db->prepare('SELECT * FROM example WHERE MATCH(some_column) AGAINST(CONCAT('"',:some_term,'"')');
$stmt->bindParam(':some_term', $some_term, PDO::PARAM_STR);
$stmt->execute();

According to the MySQL documentation on Boolean Full-Text Searches:

A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than the minimum length of indexed words, the result is empty.

Wednesday, March 31, 2021
 
VitaCoco
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :