Use PDO prepared statements in function

Question

Use PDO prepared statements in function

Navigation

#1 by (1 votes)

4

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?

Thanks!

EDITED

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. ")");

        var_dump($sql);
        //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?

php pdo

asked by anonymous 18.04.2015 / 16:50

1 answer

Update Controls in Custom Toolbox Control Colorbox responsive

score 1 · Answer 1

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("mysql:host=mysql.seudominio.com.br;dbname=baseDeDados", "Usuario", "Senha");
$statement = $pdo->prepare("Insert into geo values(
                                :latitude,
                                :longitude,
                                :accuracy,
                                :altitude,
                                :altitudeAccuracy,
                                :heading,
                                :speed,
                                :timestamp,
                                :now)");
    $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);
    $statement->execute();