mysqli : Strict Standards: Only variables should be passed by reference

Question

I'm trying to create a small SQL query class.

Here is my Class but i don't why, I've this error : Strict Standards: Only variables should be passed by reference in line 52

Line 52 is :

if (!$stmt->bind_param($param[$i][0], mysqli_real_escape_string($this->mysqli, $param[$i][1]))) {

My code (i'm beginning) :

<?php
class Sql{

    private $db;
    private $user;
    private $pwd;
    private $url;

    private $param;

    private $mysqli;

    function __construct($db, $user, $pwd, $url){
        $this->db = $db;
        $this->user = $user;
        $this->pwd = $pwd;
        $this->url = $url;


    }

    /**
     * mysqli::connection()
     * 
     * @return 
     */
    public function connection()
    {
        try{
            $this->mysqli = new mysqli($this->db, $this->user, $this->pwd, $this->url);
        }catch(Exception $e){
            throw new Exception("Impossible de se connecter à la base " . $this->db);
        }
    }

    public function select($query, $param, $debug=false){

        $this->connection();

        $r = $this->InitialiseResult("select");

        if (!($stmt = $this->mysqli->prepare($query))) {
            echo "Echec de la préparation : (" . $this->mysqli->errno . ") " . $this->mysqli->error;
        }

        //Param
        for($i=0;$i<sizeof($param);$i++){
            if (!$stmt->bind_param($param[$i][0], mysqli_real_escape_string($this->mysqli, $param[$i][1]))) {
                echo "Echec lors du liage des paramètres : (" . $stmt->errno . ") " . $stmt->error;
            }
        }

        if (!$stmt->execute()) {
            echo "Echec lors de l'exécution : (" . $stmt->errno . ") " . $stmt->error;
        }

        if (!($res = $stmt->get_result())) {
            echo "Echec lors de la récupération du jeu de résultats : (" . $stmt->errno . ") " . $stmt->error;
        }else{

            $r["state"] = true;
            $r["rows"] = $res->fetch_assoc();
            $r["num_rows"] = $res->num_rows;

            if($debug)
                var_dump($r);

        }

        return $r;

    }


    /**
     * mysqli::InitialiseResult()
     *
     * @param mixed $p
     * @return
     */
    public function InitialiseResult($p)
    {
        $r = array(); //on écrase
        $r["state"] = false;

        switch($p){
            case "select":

                $r["rows"] = array();
                $r["num_rows"] = 0;
                break;

        }

        return $r;
    }
}
?>

I've try to put $param in a property and use that is mysqli_real_escape_string() but the error is still there.

Any ideas?

You don’t need `mysqli_real_escape_string` with prepared statements. Just pass the variable directly. — Gumbo, May 24 '14 at 09:27
possible duplicate of [Strict Standards: Only variables should be passed by reference](http://stackoverflow.com/questions/2354609/strict-standards-only-variables-should-be-passed-by-reference) — Lorenz Meyer, Jun 21 '14 at 08:28

lafor · Accepted Answer · 2014-05-24T09:52:44.767

2

$stmt->bind_param() requires all params to be passed by reference, so you can't pass function's return value directly (without assigning it to a variable first, that is). But, as was already mentioned in the comments, you don't need to escape the parameters at all, that's one of the advantages of using prepared statements.

edited May 24 '14 at 09:52

answered May 24 '14 at 09:32

lafor

12,472
4
32
35

Ok, thanks. It's my first time with prepared statements but it's really useful. Thanks again. – Portekoi May 24 '14 at 09:34
NB that avoiding SQL injection isn't the *whole* point of prepared statements! – lonesomeday May 24 '14 at 09:48
@lonesomeday Yeah, I realized **whole** is not the most fortunate word the second I submitted the answer, but I was too lazy to edit. I guess I'll do that now :-) – lafor May 24 '14 at 09:50

score 1 · Answer 2 · answered May 24 '14 at 09:43

mysqli_stmt::bind_param expects the second and any following parameters to be a variable as it’s passed by reference, which is denoted by the & before the parameter:

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

Internally, PHP does not store the actual value but only a reference to the variable holding the value. And the actual value is only fetched when the prepared statement is executed. That’s why you can’t bind values but only variables.

However, due to this, it is possible to do the following:

$stmt = $mysqli->prepare('INSERT INTO table (a, b) VALUES (?, ?)');
$stmt->bind_param(1, $a);
$stmt->bind_param(2, $b);

$pairs = array(
    array('a1', 'b1'),
    array('a2', 'b2'),
    array('a3', 'b3'),
);    

foreach ($pairs as $pair) {
    list($a, $b) = $pair;
    $stmt->execute();
}

The INSERT statement is prepared and its parameters are bound only once but it’s executed with different parameter values multiple times. Changing the variable values does not destroy the variable reference.

However, as to your actual problem, you don’t need and shouldn’t use mysqli_real_escape_string on parameters for prepared statements at all. Just bind the variables and MySQLi does the rest.

mysqli : Strict Standards: Only variables should be passed by reference

2 Answers2

Linked