How to set SQL mode when using PDO?

Question

I am trying to set SQL modes, I cannot figure out how to do this using PDO. I am trying to set traditional mode in MySQL and not allow invalid dates.

Can anyone help?

Answer 1

When setting sql_mode at run time, you can use the optional "SESSION" variable. Therefore, this will not affect other customers. You can set SESSION sql_mode and then return it to the previous value after the query is completed. This way you can set sql_mode for a specific operation.

From the MySql manual:

"You can change the SQL mode at run time using SET [GLOBAL | SESSION] sql_mode = 'modes' to set the sql_mode system cost. To set the GLOBAL variable, the SUPER privilege is required and affects the operation of all clients that connect from now on. Setting the variable SESSION only affects the current client. Any client can change their sql_mode session 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 save the default value as a class variable. setSqlMode () will allow you to set SESSION sql_mode to a (VALIDATED) custom value. resetSqlMode () sets the default SESSION sql_mode. I use the SESSION variable when working with sql_mode anytime.

Then you can do something like the following. Note that this is only psuedocode; in my example, there is nothing 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; } } 

Answer 2

This only applies to your connection. The command will be launched as soon as the 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. SQL Server Modes

Answer 3

 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; }