PDO identify missing bound variables

Question

One of the more tedious to work with PDO is that it says that some variables are missing

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

... but does not tell which ones. Is there any solution to identify them? Eg

$sql = "SELECT id, name WHERE id = :id AND name like  :search_tem";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', '1');
$stmt->execute(); // throws exception - "**search_term missing"

It's so obvious everyone needs something like this. But I can't a simple solution.

Answer 1

Because of my low reputation I can't comment directly. So imagine this as a reply to the Post of Ragen Dazs on Jan 19 '13 at 15:58. I know this Topic is a few days old, but if some one like me stumble up on this by a google search...

How ever, I had problems with the regular expression in the third last line. As you can see in this example, the expression matches also time values with 00:00:00 as time. So I suggest to use the regular expression from this example.

I also wanted to know if there are needless parameters. This is how I did it, it takes a SQL query like that one in the examples above and a parameter array (See php doc example #2 for PDOStatement::execute).

    /**
 * Checks an parameter array against the sql query. it will tell you if there are any missing or needless parameters
 *
 * @param string $query      Sql query
 * @param array  $parameters Parameters array
 *
 * @return bool|array Returns TRUE if no missing or needless parameters where found or a list with the missing
 *                    or needless parameters
 */
private function checkParameters($query, $parameters)
{
    $parameterTMP = $parameters;
    $parameterCount = count($parameterTMP);
    $regexMatchCounter = preg_match_all("/:[^]\\D\\w*/", $query, $regexMatches);

    // if there are parameter in the $parameters array oder parameters in the sql query
    if( $parameterCount > 0 || $regexMatchCounter > 0 )
    {
        // take every parameter found in the sql query
        foreach( $regexMatches[ 0 ] as $parameterName )
        {
            // check if the required parameter is in the parameters array
            if( !array_key_exists($parameterName, $parameters) )
            {
                // if it is not in the parameters array, add it to the list of missing parameters
                // and continue with the next parameter from the query
                $result[ 'missing' ][] = $parameterName;
                continue;
            }

            // if the required parameter is in the parameter array, delete it from this array
            // so we get a list of parameters that are needless
            unset($parameterTMP[ $parameterName ]);
        }

        // check if there are (needless) parameters left
        if( count($parameterTMP) > 0 )
        {
            // if so, add them to the list of needles parameters
            $result[ 'needless' ] = array_keys($parameterTMP);
        }

        // if at this point $result is an array,
        // some parameters are missing or needless, so we return the result list(s)
        if( isset($result) && is_array($result) )
        {
            return $result;
        }
    }

    // if we reach this point, no missing or needless parameters where found,
    // you are good to go
    return true;
}

If some one want it to throw an exception if something is wrong, just replace "return $result;" with the following lines of code:

    $missingCount = 0;
    $missing = "";

    $needlessCount = 0;
    $needless = "";

    if( array_key_exists('missing', $parameters) )
    {
        $missingCount = count($parameters[ 'missing' ]);
        $missing = " (" . implode(", ", $parameters[ 'missing' ]) . ") ";
    }

    if( array_key_exists('needless', $parameters) )
    {
        $needlessCount = count($parameters[ 'needless' ]);
        $needless = " (" . implode(", ", $parameters[ 'needless' ]) . ")";
    }

    $msg = "There are " . $missingCount . " missing parameter(s)".$missing." and ".$needlessCount." needless parameter(s)".$needless.".";

    throw new Exception($msg);

have fun.

Answer 2

Updated Answer

Having misread the question first time round, I can now understand what it is you are wanting. As far as I can see, this is not possible as the error messages are defined by the PDO Exception.

However, a workaround could be to write a wrapper class for the PDO, and check the the bound values and query yourself, then if there are missing values, throw your own exception. This is probably the best way of achieving this...

Original Answer

It's so obvious everyone needs something like this. But I can't a simple solution.

I disagree with this, adding this sort of functionality would be pointless. Consider this:

"SELECT id, name WHERE id = '1' AND name like  "

This is what your code would output if it was allowed! Clearly the above would not work as nothing has been written in the LIKE term.

I can understand why you want this, as I too have forgotten (intentionally and unintentionally) to bind values in the past, however, there is actually no benefit as the query will simply not work if you do not bind the values.

You would be much better off building the query dynamically like so:

// Gather the where parameters
$get_data_where = array();

// Field 2
if(!empty($parameters['field_1'])){
    $get_data_where[':field_1'] = '%'.$parameters['field_1'].'%';
}           
// Field 2
if(!empty($parameters['field_2'])){
    $get_data_where[':field_2'] = $parameters['field_2'];
}   

// Combine the where array with the bind valus array
$this->mssql->bind_values = array_merge($this->mssql->bind_values,$get_data_where);

// Prepare the SQL to gather the data
$SQL  = "SELECT * \n";
$SQL .= "FROM [Some_Table] AS ST \n";

// Append the where statement if necessary
// Build the where statement based on bind values
if(!empty($get_data_where)){
    $SQL .= "WHERE \n\t";

    // Field 1
    if(!empty($this->mssql->bind_values[':field_1'])){
        $SQL .= $where_and."ST.[Field_1] LIKE :field_1 \n\t";
        // Separate the where conditions
        $where_and = "AND ";
    }               
    // Field 2
    if(!empty($this->mssql->bind_values[':field_2'])){
        $SQL .= $where_and."ST.[Field_2] = :field_2 \n\t";
        // Separate the where conditions
        $where_and = "AND ";
    }
}

Answer 3

I would say that named placeholders were invented by PDO team especially for this purpose - to ease visual verification of the query and placeholders.

I have to agree, such a feature could be some sort of syntax error sugar, but honestly, I don't find it too useful. There are other errors that are much more puzzling (like you have an error near '' one) while to find missing placeholder is not quite a big deal.

Answer 4

After some days searching about this subject, testing some stuff I found this solution - follow the code from bind method from my Database class:

// [Method from Class Database]

/**
 * @param PDOStatement stmt
 * @param array data
 * @throws Exception if some variables are missing when interpolate query
 * @example $db->bind($stmt, $_POST)
 */
function bind(&$stmt, $data) {
    $sql = $stmt->queryString;
    // Sort $data keys to prevent erroneus bind parameters
    ksort($data);
    $data = array_reverse($data);
    foreach ($data as $_key => $_val) {
        if (preg_match("/:$_key/", $sql)) {
            $stmt->bindParam(":$_key", $data[$_key]);
            $sql = preg_replace("/:$_key/", $this->dbh->quote($data[$_key]), $sql, 1);
        }
    }

    //  if ($this->debug) {
    //      $this->fb->info($sql, 'SQL');
    //      $this->fb->info($stmt, 'Statment');
    //      $this->fb->info($data, 'Raw Data');
    //  }

    if (strpos($sql, ":")) {
        $matches = array();
        $this->dbg = preg_match_all('/:[A-Za-z0-9_]*/', $sql, $matches);
        throw new Exception('PDO Missing variables: ' . implode(', ', $matches[0]));
    }
}

This code needs revision, if someone finds a bug or have improved something please share!