Use PDO prepared statements in function


I've been reading about preventing sql injections and seen using prepared statements might help.

I have the following function in a class for crud that I have developed:

public function inserir($tabela, $campos, $dados) {
    try {
        $campos = implode(", ", $campos);
        $dados = implode(", ", $dados);

        $sql = $this->pdo->prepare("INSERT INTO " .$tabela. " (" .$campos. ") VALUES (" .$dados. ")");

        return $sql->execute();
    } catch (PDOException $e) {
        echo "Ocorreu um erro: " .$e->getMessage();

And to use it just do as in the example below:

$teste = array("nome", "sobrenome", "email");
$testeR = array("'Igor'", "'Teste'", "'[email protected]'");
$bd -> inserir("tabela", $teste, $testeR);

And it works correctly.

However, I would like to use bindValue , but I do not know how to proceed. The only "idea" I have is that I'll have to make a foreach for each item, but I really have no idea how I'll do it. How to do it right?



I modified some things according to the answer below (from @ray) and my code looks like this:

In class:

public function inserir($tabela, $dados) {
    try {
        $campos = implode(", ", array_keys($dados));
        $valores = implode(", ", array_values($dados));

        $totalInterrogacoes = count($dados);

        $interrogacoes = str_repeat("?,", $totalInterrogacoes);
        $interrogacoes = substr($interrogacoes, 0, -1); // remove a última virgula

        $sql = $this->pdo->prepare("INSERT INTO " .$tabela. " (" .$campos. ") VALUES (" .$interrogacoes. ")");

        //return $sql->execute();
    } catch (PDOException $e) {
        echo "Ocorreu um erro: " .$e->getMessage();

And in another test file:

$teste = array("nome" => "Igor", "sobrenome" => "Teste", "email" => "[email protected]");
$bd -> inserir("tabela", $teste);

Then it works correctly, and var_dump($sql) returns the following:


object (PDOStatement) # 3 (1) {     ["queryString"] = >     string (58) "INSERT INTO table (name, surname, email) VALUES (?,?,?)"   }

However, now the big question arises again: How to make bindValue of each question for its respective value?

asked by anonymous 18.04.2015 / 16:50

1 answer


In fact, this is not the right way.

The php PDO already supports variable bindings without having to edit the query.

See the example below. (It is an excerpt of a service that writes GPS data to a table in MySql.)

$Geo = new Geo();
$Geo->latitude = $_GET["latitude"];
$Geo->longitude = $_GET["longitude"];
$Geo->accuracy = $_GET["accuracy"];
$Geo->altitude = $_GET["altitude"];
$Geo->altitudeAccuracy = $_GET["altitudeAccuracy"];
$Geo->heading = $_GET["heading"];
$Geo->speed = $_GET["speed"];
$Geo->timestamp = $_GET["timestamp"];

$pdo = new PDO(";dbname=baseDeDados", "Usuario", "Senha");
$statement = $pdo->prepare("Insert into geo values(
    $statement->bindParam(':latitude', $Geo->latitude);
    $statement->bindParam(':longitude', $Geo->longitude);
    $statement->bindParam(':accuracy', $Geo->accuracy);
    $statement->bindParam(':altitude', $Geo->altitude);
    $statement->bindParam(':altitudeAccuracy', $Geo->altitudeAccuracy);
    $statement->bindParam(':heading', $Geo->heading);
    $statement->bindParam(':speed', $Geo->speed);
    $statement->bindParam(':timestamp', $Geo->timestamp);
    $statement->bindParam(':now', $Geo->now);
18.04.2015 / 23:51