PDO SET SESSION AND SET NAMES in MYSQL_ATTR_INIT_COMMAND

Question

I'm trying to put my UTF-8 connection but I can not, for some reason my system breaks down if I remove SET SESSION in MYSQL_ATTR_INIT_COMMAND then I can not change this MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'

This works, look good special characters, but can not create new accounts:

array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

This is my code:

<?php
class DB {
    private static $_instance = null;
    private $_pdo, $_query, $_error = false, $_results, $_resultsArray, $_count = 0, $_lastId, $_queryCount=0;

    private function __construct(){
        try{
            $this->_pdo = new PDO('mysql:host=' .
                Config::get('mysql/host') .';dbname='. 
                Config::get('mysql/db'), 
                Config::get('mysql/username'), 
                Config::get('mysql/password'),
                array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''"));
        } catch(PDOException $e){
            die($e->getMessage());
        }
    }
}

Any idea how to implement it without breaking my code?

I usually do it like this: `$db = new PDO('mysql:host=127.0.0.1;dbname=whatever;charset=utf8', 'root', '');`. — Dan Costinel, Oct 17 '16 at 01:45
Check this answer: http://stackoverflow.com/a/7325424/4548751 — Dan Costinel, Oct 17 '16 at 01:55

Your Common Sense · Answer 1 · 2016-10-17T06:12:13.750

It always made me wonder, why PDO users are so obsessed with running a regular SQL query some tricky way, instead of just running it, using the regular function intended for this task.

private function __construct(){
        $this->_pdo = new PDO('mysql:host=' .
            Config::get('mysql/host') .';dbname='. 
            Config::get('mysql/db'), 
            Config::get('mysql/username'), 
            Config::get('mysql/password'),
            array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
        $this->_pdo->query("SET SESSION sql_mode = ''");
        $this->_pdo->query("SET NAMES utf8");
}

You see, after connecting, you can run as many additional SQL queries as you need.

Note that if setting charset in DSN doesn't work for you, it means that your PHP version is awfully outdated, and you should upgrade as soon as possible. Not for the charset issue of course, but for all the security fixes that has been issued during all these years since your version has been abandoned.

Also note that your class suffer from many design flaws. I recommend you to read the article I wrote, Your first database wrapper's childhood diseases and fix issues with error reporting, stateful variables and many other issues that, I am sure, exist in the rest of your class

PDO SET SESSION AND SET NAMES in MYSQL_ATTR_INIT_COMMAND

1 Answers1