MySQL query returning weird error

Question

So I am trying to insert some data into a database with my own mysql class (built on top of pdo) but I keed getting some weird errors

( ! ) Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'Google Chrome' for column 'user_id' at row 1 in C:\wamp64\www\LVWeb\Core\Database\database.mysql-pdo.php on line 44

( ! ) PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'Google Chrome' for column 'user_id' at row 1 in C:\wamp64\www\LVWeb\Core\Database\database.mysql-pdo.php on line 44

Table structure: http://prntscr.com/c3pgzi

My Query function:

public function Query($query, $vars = []){
        $Statement = $this -> con -> prepare($query);

        if(is_array($vars))
            foreach($vars AS $key => $val){
                if(is_string($val))
                    $Statement->bindParam($key, $val, PDO::PARAM_STR);
                else if(is_integer($val))
                    $Statement->bindParam($key, $val, PDO::PARAM_INT);
            }

        return $Statement->execute();
    }

The part where I try to insert the data:

$a = "INSERT INTO sessions (ses_id, user_id, ip, os, browser) VALUES
    (:ses_id, :user_id, :ip, :os, :browser)";

$this->DB->Query($a, [
    ':ses_id'   =>  $session,
    ':user_id'  =>  $response['ID'],
    ':ip'       =>  GetIp(),
    ':os'       =>  GetOS(),
    ':browser'  =>  GetBrowser()['name']
]);

So I have tested every variable out to see if they are the right type:

$session = string
$response['ID'] = integer
GetIp() = string
GetOs() = string
GetBrowser()['name'] = string

Weird errors? They can't be clearer.. it even tells you. You tried to supply Google Chrome for an integer. You made a classic mistake trying to make PDO "better" by creating some silly code that just makes it worse. Dump your function, use prepared statements directly. — N.B.

Andrey Tserkus Andrey Tserkus · Accepted Answer · 2016-08-09T22:42:23

This is a funny issue - the cause is that the parameters are bound as references in PDOStatement->bindParam().

I.e. in the foreach loop you say the following: "let :ses_id be bound to whatever is in $val now", then on the next iteration - "let :user_id be bound to whatever is in $val now", etc. At the the end of the loop all parameters are effectively bound to the same thing - variable $val. And what does that value contain? The last thing that PHP assigned to it - $vars[':browser'].

Thus $Statement->execute() tries to insert a record with all the fields equal to the name of the browser.

You need to change the code of foreach to the following: foreach($vars AS $key => $val){ if (is_string($val)) $Statement->bindParam($key, $vars[$key], PDO::PARAM_STR); else if(is_integer($val)) $Statement->bindParam($key, $vars[$key], PDO::PARAM_INT); }

MySQL query returning weird error

1 Answers