Edit report at https://bugs.php.net/bug.php?id=54861&edit=1
ID: 54861
Updated by: u...@php.net
Reported by: harrieva at gmx dot de
Summary: query() optionaly prepared and
PDO::PARAM_FIELDNAME(quoting)
Status: Open
Type: Feature/Change Request
Package: PDO related
Operating System: linux
PHP Version: 5.3.6
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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] johan...@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
