How can I test if PDO successfully binded my variables?

Question

//The variables are declared outside this function. I use $_POST to retrieve each user input in another .php file using html form tag. The variable $db is my database connection.

function insertintodb ($db, $avar, $bvar, $cvar) { /* How can I tell what values my variables are when using PDO bindParam? I output $avar to see its value in this function. How can I tell if PDO actually binded ":firstname" to $avar? Likewise with the other variables. */

echo 'before <br>';
echo $avar;
echo '<br>';

    //firstname, midinitial, lastname are values in my database.
    //name is my table I am inserting into.
$insertname = "INSERT INTO name (firstname, midinitial, lastname) 
VALUES (:firstname, :midname, :lastname)";

echo 'before PDO prepare<br>';
echo $avar;
echo '<br>';

$stmt = $db->prepare($insertname);

$stmt->bindParam(':firstname', $avar);
$stmt->bindParam(':midname', $bvar);
$stmt->bindParam(':lastname', $cvar);

echo 'after binding variables using bindParam <br>';
echo $avar;
echo '<br>';

$stmt->execute();

echo 'after executing <br>';
echo $avar;
echo '<br>';

}

Answer 1

bindParam() returns true or false:

if($stmt->bindParam(':firstname', $avar)) {
    echo 'Woo hoo yay!';
} else {
    echo 'Boo hoo waa';
}

Answer 2

Just avoid bindParam at all. this will relieve you from burden of checking its result

function insertintodb ($db, $avar, $bvar, $cvar)
    $sql = "INSERT INTO name (firstname, midinitial, lastname) VALUES (?, ?, ?)";
    $stmt = $db->prepare($sql);
    $data = array_slice(func_get_args()); // lets strip $db from the func args
    $stmt->execute($data);
}

Answer 3

Trust PDO

If PDO has a bug, this is not your problem. It is PDO's - since it's fairly well tested, there are very few bugs in current PDO versions. That is, if you tell PDO to bind, then trust that it will bind (PDO will fail on execute if there are unbound parameters, so we don't even have to "trust" it too much).

However, use PDOStatement::bindValue (not bindParam, except in special cases) because bindValue will ensure the value supplied is bound and not merely a "reference [to the variable]". This prevents "accidental changes" to variables between binding and execution from affecting the query.

Write and Test a DAL

Write a Data-Access Layer (DAL)¹, as opposed to inline spaghetti SQL, and then test it. While ensuring the parameter is "actually binded" sounds useful, it isn't doesn't ensure the code is valid semantically. For instance, what if the code incorrectly did $stmt->bindParam(':firstname', $lastname);?

Furthermore, PDO itself will fail (I recommend enabling Exceptions) on most basic "binding failures" (such as unbound parameters or nonconvertible values) when the query is executed, making the very nature of testing if a parameter is "actually binded" less important.

Since detecting binding is not relevant to determining the validity of the code, nor can PDO report exactly what data is stored due SQL conversion issues (including truncation), then the problem really isn't about checking each bind, it's about checking operations - and a DAL provides guaranteed contracts for different operations.

---

¹ A DAL doesn't have to be scary, nor does it have to use "ORM" or "OOP" or "DI/IOC" or anything else (although an advanced DAL may use all of those "concepts"). Consider, for starters, a small handful of functions in a separately included file which are the only mechanism for connecting to the database and "talking to" SQL.

Each of these functions then has a simple contract (which as documentation on the top) which defines the parameters it takes, the results it returns, and any exceptions it may throw.

Congratulations, you've created a minimal and testable DAL!

Then this DAL, which is just a collection of functions for now, can be taken and tested/verified outside of the "actual program" (preferably using an existing test framework/harness).