How can I set the SQL mode while using PDO?

I am trying to set SQL modes, I can't figure o开发者_C百科ut how to do it using PDO. I am trying to set Traditional mode in MySQL and not allow invalid dates.

Can anyone help?

---

This applies to only your connection. The command will be run as soon as PDO connects:

$pdo = new PDO(
     $dsn, 
     $username, 
     $password, 
     array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="TRADITIONAL"') 
);

See PHP: MySQL (PDO)
See 5.1.6. Server SQL Modes

---

You can use the optional 'SESSION' variable when setting the sql_mode at runtime. That way it won't affect other clients. You can set the SESSION sql_mode and then set it back to the previous value after your query has completed. In this way, you can set the sql_mode for a specific operation.

From the MySql manual:

"You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system value. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time."

I personally added some methods to my database class to handle this. initSqlMode() will execute the query 'SELECT SESSION.sql_mode' and store the default value as a class variable. setSqlMode() will allow you to set the SESSION sql_mode to a (VALIDATED) custom value. resetSqlMode() sets the SESSION sql_mode back to the default value. I use the SESSION variable when manipulating the sql_mode at all times.

Then you can do something like the following. Note this is only psuedocode; there is nothing in my example to prevent sql injection or parameterize the sql query.

$db = new database();
$badqueryresult = $db->executeStrict('BAD SQL QUERY');
Class database {
     ...
     function executeStrict($query){
      $this->initSqlMode();
      $this->setSqlMode('STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION');
      $result = $this->Execute($query);
      $this->resetSqlMode();
      return $result;
     }
}

---

function get_pdo($c =false){

    if (!$c){$c=get_config();}
    $pdo=new PDO($c['dsn'] , $c['db_user'], $c['db_password']);
    $better_sql_defaults=array (
        'SET SESSION sql_warnings=1',
        'SET NAMES utf8',
        'SET SESSION sql_mode = "ANSI,TRADITIONAL" ',
    );

    // throw an exception on errors
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    foreach ($better_sql_defaults as $sql){
        $pdo->query($sql);
    }
    return $pdo;
}

---

Use this to clear the sql_mode variable:

$db_conn->query("SET SESSION sql_mode=''")->execute();

Or this to remove a specific item from it:

$db_conn->query("SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'WRITE_HERE_THE_NAME',''));")->execute();

If you want to make the change global, you would need to have some admin privileges.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'WRITE_HERE_THE_NAME',''));