Can I do PDO prepare statements with array and foreach loop

Question

So I legit started to read about PDO and PDO statemnts to update my code and give it more security, but I don't really know if this is ok or is it possible to make injections. I also sanitize the data before making the array. I made this function o add data given the name table and an array with the columns names and values like this

$tabla = 'tabla'
$datos = array(
        'id' => 'NULL',
        'id_usuario' => "'{$usuario['id']}'",
        'id_tipo' => "'{$tipo}'",
        'id_estado' => "'{$estado}'",
        'id_visibilidad' => "'{$visibilidad}'",
        'titulo' => "'{$titulo}'",
        'contenido' => "'{$contenido}'",
        'fecha' => 'CURRENT_TIMESTAMP'
      );

function pdo_agregar($tabla, $datos) {
  global $pdo;
  $columnas = implode(', ', array_keys($datos));
  $valores = implode(', ', array_values($datos));
  $sentencia = $pdo->prepare("INSERT INTO $tabla ($columnas) VALUES ($valores)");
  foreach($datos as $indice => $valor) {
    $sentencia->bindParam(':'.$indice, $valor);
  }
  if($sentencia->execute()) {
    return true;
  } else {
    return false;
  }
  $sentencia->close();
  $pdo->close();
}

Is this okay? o Do i have to make statements like

$sentencia = $pdo->prepare("INSERT INTO $tabla (col, col2, col3) VALUES (:col, :col2, :col3");

And then bind params individually?

Thanks in advance!

Answer 1

You don't need these bind things, you can throw directly an array to the execute function of PDO (which does this binding for you).

My array is as follow ["data" => "mydata", "data2" = "mysecond"] so I would generate a query like:

INSERT INTO `Tab`(`data`, `data2`) VALUES (:data, :data2)

And the code would be:

        foreach($data as $key=>$val){
            $keys[] = $key;
            $vals[] = ":".$key;
        }
        $keys = "`".implode("`,`", $keys)."`";
        $vals = implode(",", $vals);
        $sql = "INSERT INTO `".$tab."`(".$keys.") VALUES (".$vals.")";
        $prep_insert = $this->pdo->prepare($sql);
        $prep_insert->execute($data);

By the way, you should use classes and this, instead of this global things.