Error Checking for PDO Prepared Statements

Question 1

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.

Question 2

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

Question 3

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

Question 4

Your ON DUPLICATE KEY syntax is not correct.

$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)
    ON DUPLICATE KEY UPDATE fname= :fname2, lname= :lname2');

$stmt->bindParam(':user_id', $user_id);  
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);      
$stmt->bindParam(':fname2', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname2', $_POST['lname'], PDO::PARAM_STR);

You don't need to put the table name or SET in the ON DUPLICATE KEY clause, and you don't need a WHERE clause (it always updates the record with the duplicate key).

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

You also had a PHP syntax error: you split the query up into two strings.

UPDATE:

To bind multiple parameters:

function bindMultiple($stmt, $params, &$variable, $type) {
  foreach ($params as $param) {
    $stmt->bindParam($param, $variable, $type);
  }
}

Then call it:

bindMultiple($stmt, array(':fname', ':fname2'), $_POST['fname'], PDO::PARAM_STR);

Question 5

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Question 6

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

Error Checking for PDO Prepared Statements

Answers

php,mysql