How important is using PDO binding methods?

[NotionCommotion]

I only use : and ? for prepared statements never use bindValue() or bindParam().

 

One "trick" I have done when the where statement uses IN is as follows:

$list=rtrim(str_repeat('?,',count($d)),',');
$sql="SELECT a, b, c FROM my_table WHERE id IN ($list)";
$stmt = $this->pdo->prepare($sql);
$stmt->execute($d);

Another one for non-complicated inserts with a bunch of columns is a method which is passed the table name and an associated array where the array keys are used as column names.

 

    protected function insertDB($table,array $data)
    {
        /* $data is an array who's indexes are table columns and values are validated data to insert */
        $columns1=[];
        $columns2=[];
        foreach($data as $key=>$value) {
            $columns1[]=$key;
            $columns2[]=':'.$key;
        }
        $columns1=implode(',',$columns1);
        $columns2=implode(',',$columns2);
        $stmt = $this->pdo->prepare("INSERT INTO $table ($columns1) VALUES($columns2)");
        return $stmt->execute($data);
    }

Recently, when using this method, one of the values in the array was Boolean, and the associated SQL column was BOOL, and the query failed.

 

Am I living dangerously not using one of the bind methods all the time?  What are the particular circumstances that I should be concerned?

 

Can my insertDB() method be adapted to do so?  I am thinking that thee foreach loop can check the type of the array value, and bind accordingly.  But then again, PHP is so loose that maybe doing so is not smart.

 

Thanks

 

 

[Jacques1]

Choosing the parameter type based on the PHP type doesn't make sense, because there is no simple one-to-one mapping. For example, large MySQL integers don't fit into PHP integers and must be stored in strings instead.

 

So there are two options: Either you explicitly provide the types together with the input data. Or you cast the parameters in the query.

... CAST(:param AS INT) ...