Req #54861 [Opn->Nab]: query() optionaly prepared and PDO::PARAM_FIELDNAME(quoting)

Edit report at https://bugs.php.net/bug.php?id=54861&edit=1 ID: 54861 Updated by: johannes@php.net Reported by: harrieva at gmx dot de Summary: query() optionaly prepared and PDO::PARAM_FIELDNAME(quoting) -Status: Open +Status: Not a bug Type: Feature/Change Request Package: PDO related Operating System: linux PHP Version: 5.3.6 Block user comment: N Private report: N New Comment: Your problem can be solved simpler using something like class YourPDO { public function __construct($dsn, $user, $pass, $options = array()) { if (isset($options[PDO::MYSQL_ATTR_INIT_COMMAND])) { $options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET SESSION sql_mode = 'ANSI_QUOTES';".$options[PDO::MYSQL_ATTR_INIT_COMMAND]; } else { $options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET SESSION sql_mode = 'ANSI_QUOTES'"; } parent::__construct($dsn, $user, $pass, $options); } public function query($query, $params = null) { if ($params) { $stmt = $this->prepare($query); if ($stmt->execute($params)) { return $stmt; } else{ return false; } } else { return parent::query($query); } } } The PDO interface is supposed to be quite small .. Previous Comments: ------------------------------------------------------------------------ [2012-05-04 09:26:37] uw@php.net PDO::quote() places quotes around the *input string* (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver. PDO has never been about aligning SQL differences. And, I also want to stress out that MySQL does support the quoting you want, http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_ansi_quotes . It is *your* task to setup MySQL appropriately. ------------------------------------------------------------------------ [2011-05-26 16:30:29] harrieva at gmx dot de 1. SQL-Quoting: Postgresql whants a query like this: Select "Name" from "Persons" Mysql wants the same query like this: Select Name from Persons Mysql has a unique interpretation of the Standard by default. When i want to write a query whitch runs on mysql and other sql-servers i have to quote fielnames (and tablenames) diffrent. In my eyes this is something that should be done by PDO->quote(). (This is importend for captalized fieldnames) 2. Queryparameter: The second thing is an idea i had. This idea is on quoting to. Here is an example: How it is often done: $sql = "select * from a where bla = " . $bla; $res = $db->query($sql); How it should be done: $sql = "select * from a where bla = " . $db->quote($bla); $res = $db->query($sql); How should be done (the nicer way): $stmt = $db->prepare("select * from a where bla = ?"); $stmt->execute($bla) And now i like it to be done: $stmt = $db->query("select * from a where bla = ?",$bla); I like the ? and :-Syntax that i can use with prepared statements. And i like to use this syntax in query() too. Like prepare(), query() returns a PDO::Statement, so my idea is, that query() should return an executed prepared statement, when a second parameter is given. It saves one line of code and it feels smother, then getting an object back, call execute() for this object, and then call fetchall() on the same object. Back in the days you mysql returnd resultsets, and so people are still used to the thinking that db returns results, The Statement-Objects are diffrent, but most people do not recordnice it because they only use query(), Furthermore i think many people use prepare() only when a sql is used more then one time. This is psychological, and so they don't use the advantages of the ? and :-Syntax, because query() does not support it... I hope everything is clearer now ... ? ... regards, Hendrik ------------------------------------------------------------------------ [2011-05-26 14:54:26] johannes@php.net I do not understand what you want. Could you be more precise please? About the " vs. ` thing: You can set the SQL mode in MySQL to be more standards compliant. The MySQL developers are conservative in changing the default as it will break many applications unfortunately. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html (can be set per session if you don't want/can change it globally) ------------------------------------------------------------------------ [2011-05-19 13:33:54] harrieva at gmx dot de Description: ------------ I like prepared statements and its templating. Since query returns a statement, why not making it a prepared one, when the second parameter is an array, and execute it directly... Example: See my exPDO-Class at the bottom. Since mysql quotes fieldnames(and tablenames) different then standardconform sqlservers, it is not easy to write/generate sql that work everywhere... Eg. postgre lowercases fieldnames when they are not quoted in "... Mysql wants `... I help my self by deriving from PDO and overwrite quote... public function quote($txt,$parameter_type = PDO::PARAM_STR ){ if($parameter_type == "12345"){ if($this->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql'){ return '' . $txt . ''; }else{ return '"' . $txt . '"'; } }else{ return parent::quote($txt,$parameter_type); } } By the way: Here is my hole extention.... Now it is possible to see all the executed querys, and the time it took to get the result.... <?php class extPDO extends PDO{ public $query_count = 0; public $exec_count = 0; public $prepared_count = 0; public $query_time = 0; public $sqls = array(); public function __construct($dsn, $username, $passwd, $options=array()){ parent::__construct($dsn, $username, $passwd, $options); self::setAttribute(PDO::ATTR_STATEMENT_CLASS, array("extPDOStatement",array($this))); } public function query($statement,$args = array()){ $this->query_count++; if(is_array($args)){ if(empty($args)){ $this->sqls[] = 'q: '.$statement; $start = microtime(true); $res = parent::query($statement); $this->query_time += microtime(true) - $start; return $res; }else{ //keine zeitmessung da diese durchs statement übernomen wird $res = self::prepare($statement); $res->execute($args); $this->prepared_count--; return $res; } }else{ $res = parent::prepare($statement); $res->execute(array($args)); $this->prepared_count--; return $res; } } public function exec($statement,$args = array()){ $this->exec_count++; if(is_array($args)){ if(empty($args)){ $this->sqls[] = 'e: '. $statement; $start = microtime(true); $res = parent::exec($statement); $this->query_time += microtime(true) - $start; return $res; }else{ $res = self::prepare($statement); $res->execute($args); $this->prepared_count--; return $res->rowCount(); } }else{ $res = self::prepare($statement); $res->execute( array($args) ); $this->prepared_count--; return $res->rowCount(); } } public function quote($txt,$parameter_type = PDO::PARAM_STR ){ if($parameter_type == "12345"){ if($this->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql'){ return '' . $txt . ''; }else{ return '"' . $txt . '"'; } }else{ return parent::quote($txt,$parameter_type); } } public function prepare($statement,array $options = array()){ return parent::prepare($statement,$options); } } class extPDOStatement extends PDOStatement{ private $db; protected function __construct($db){ $this->db = $db; } public function execute(array $input_parameters = array()){ $this->db->sqls[] = 'p: '. $this->queryString; $this->db->prepared_count++; $start = microtime(true); $res = parent::execute($input_parameters); $this->db->query_time += microtime(true) - $start; return $res; } } ?> ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=54861&edit=1