Use PDO Prepared statements in function

Asked

Viewed 761 times

5

I’ve been reading about prevention sql injections and saw to use Prepared statements can help with that.

I have the following function in a crud class I 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 properly.

However, I would like to use bindValue, but I don’t know how to proceed. The only "idea" I have is that I will have to do a foreach for each item, but I really have no idea how I’m going to do that. How to do it the right way?

Thank you!

EDITED

I modified some things according to the answer below (from @rray) and my code was 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);

So it works properly, and the var_dump($sql) returns me the following:

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

But now the big question arises again: How to effect the bindValue of each question for its respective value?

php oop pdo
  • 1

    You can see this in this reply: http://answall.com/a/33528/91

  • I took a look at the answer of the link you passed. But I did not understand one thing: what he is doing is the same as calling the bindValue?

  • I edited the question. I hope I can help. Thank you!

  • 1

    @Igor, '?' will be replaced by the values present within the array that is passed in execute but you can foreach over the array that will be passed and escape all values.

  • So@ricardo_henrique. I’m trying with foreach but I’m not getting it. Can you give me an example? Thanks!

  • 1

    When you pass an array to the execute() method, eh the same thing as bindValue(), the first query will be replaced by the first element of the array.

  • Ah, I got @rray! However, I tried like this: Return $sql->execute($values); and returned me the following error: : Pdostatement::execute() expects Parameter 1 to be array, string Given in (on the execute line)

  • And if I try this: Return $sql->execute(array($values)); Me returns the following error: Error occurred: SQLSTATE[HY093]: Invalid Parameter number: number of bound variables does not match number of tokens

  • 1

    Error says number of queries is different from amount of values.

  • Yes, that I understood, but I couldn’t solve!

  • I gave one var_dump(array($values)); and the return is: array(1) { [0]=> string(22) "Igor, Test, [email protected]" } Ie, it’s interpreting everything as 1 item in the array only. Now, how can I get it to interpret the 3 items in the array? Does giving the explode commas and then creating another array from them would be the right way?

  • 1

    In fact, my question has already been answered. This other case of the array I will leave to another question. Many thanks to all who collaborated.

Show 7 more comments

1 answer

1

Actually this is not the right way.

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

See the example below. (It’s a snippet of a service that records GPS data in 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();

Browser other questions tagged php oop pdo

You are not signed in. Login or sign up in order to post.