[lubosdz]

Hi,

how do you write SQL queries with properly escaped column names in WHERE condition?

I am extensively testing cross database application and find it difficult to write cross-database queries without having properly escaped column names in WHERE condition.

For example following will not work for oracle if you have created column names in lower case:

Book::model()->count(array('condition' => 'date_returned > 0'))

Column date_returned will not be properly escaped and therefore oracle will convert column into uppercased DATE_RETURNED (as it does for all objects and cannot be configured:-((( ) and SQL query will fail.

I think that escaping column names in WHERE condition is currently major drawback (perhaps last) of the YII's ORM:-(

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "DATE_RETURNED": invalid identifier
(ext\pdo_oci\oci_statement.c:146). The SQL statement executed was: SELECT COUNT(*) FROM "books" "t" WHERE date_returned > 0 

Are there any plans to support escaping in WHERE conditions?
How does Doctrine2 escape WHERE conditions?

What about optional escape syntax:

1. Book::model()->count(array('condition' => '{{date_returned}} > 0')) where {{ and }} would be replaced with escape characters,

or

2. Book::model()->count(array('condition' => array('date_returned','>','0'))) - separate operands and operator

or

3. parsing WHERE condition is unreliable and difficult but yet it could be an option as well..

How do you handle this problem?

Cheers
Lubos

[Rodrigo Coelho]

Try this to get the quoted name:

$quoted = Book::model()->getTableSchema()->getColumn('date_returned')->rawName;

[lubosdz]

mentel, on 26 May 2011 - 07:34 PM, said:

Try this to get the quoted name:

$quoted = Book::model()->getTableSchema()->getColumn('date_returned')->rawName;

Hi Mentel,

thanx for good advice.

Unfortunatelly, it helps only partially.
It does not resolve internally generated WHERE conditions. Consider following method "search" generated by gii code generator within a model class:

public function search(){

   $criteria=new CDbCriteria;
	
   $criteria->compare('id',$this->id);
   $criteria->compare('name',$this->name,true);
   $criteria->compare('code',$this->code,true);

   return new CActiveDataProvider(get_class($this), array(
	'criteria'=>$criteria,
	'sort'=>array(
		'defaultOrder'=>'t.id DESC',
	  )
));
}

Oracle always fails when it tries to look for the [id] or [name] or [code] columns, because criteria's WHERE will not be escaped and oracle will return resultset with [ID] or [NAME] or [CODE] and case sensitive PHP fails.

One must either drop support for oracle or write correctly escaped SQL queries on his own ignoring Yii's built-in AR, query builder and CDBCriteria (means significantly more development time). Actually this is to blame oracle, since it is the only database forcibly converting object names into uppercase - all other databases (tested mySQL, MSSQL, postgreSQL) can either be configured for case sensitivity or don't do any weirdo conversions.

Conclusion:
===========
If one wants to write cross-database portable application, then he MUST write all table and column names in a UPPERCASE (because of oracle and because it is hard to parse where conditions for Yii).

[genn]

I had the same problem and ended up explicitly escaping the column names in the WHERE conditions:

$quotedName = Yii::app()->db->quoteColumnName( '{{tableName}}.columnName' );

Just create a shortcut for this function and you are ready to go

[lubosdz]

Here is my final solution to writing cross-database compatible SQL queries.
Actually the only problem is, that Yii would not (or may not for some reason) quote column and table names in some AR functions.
So one has to discover those methods and painfully add proper quoting where needed.

.../app/components/ActiveRecord.php:

class ActiveRecord extends CActiveRecord{
	....
	
	/**
	* Return properly quoted/escaped column name
	* @param string $col column name like "id" or "t.id"
	*/
	public function quoteCol($col){
		return $this->getDbConnection()->quoteColumnName($col);
	}

	/**
	* Return properly quoted table name
	* @param string $name Table name
	*/
	public function quoteTable($t){
		return $this->getDbConnection()->quoteTableName($t);
	}
	
	....
}

.../app/models/InventoryModel.php:

class InventoryModel extends ActiveRecord{

	....

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search(){
	
		$criteria=new CDbCriteria( array(
			// quoting not needed
			'with' => array('category', 'supplier'),
		));

		$this->searchOutlisted = isset($_GET['searchOutlisted']) && $_GET['searchOutlisted']=='1' ? '1' : '0';
		if(!$this->searchOutlisted){
			// do not list returned items
			// we must quote column
			$criteria->addCondition($this->quoteCol('t.date_outlist').' = 0');
		}
		
		// compare method needs quoting
			
		$this->supplierName = isset($_GET['supplierName'])? $_GET['supplierName'] : null;
		$criteria->compare($this->quoteCol('supplier.name'),$this->supplierName,true);

		$criteria->compare($this->quoteCol('t.name'),$this->name,true);
		$criteria->compare($this->quoteCol('t.inventory_number'),$this->inventory_number,true);
		$criteria->compare($this->quoteCol('t.supplier_number'),$this->supplier_number,true);
		
		return new CActiveDataProvider(get_class($this), array(
			'criteria'=>$criteria,
			'sort'=>array(
				// must be quoted
				'defaultOrder'=>$this->quoteCol('t.id').' DESC',
				
				// no quoting needed
				'attributes' => array(
					'name',
					'inventory_number',
					'category.name',
					'supplier.name',
					'date_enlist',
					'price_enlist',
				),
			)
		));
	}
	
	....

}

And for SQL query builder we have to quote all columns like in following:

/**
* Return array KEY => VALUE only used departments by inventory items
* Used for assigning items to users.
*/
public function getDepartmentOptions(){
	$rows = Yii::app()->db->createCommand()
			->select( array('i.department_id', 'd.name') ) // properly quoted
			->from('im_inventory i') // properly quoted
                        // conditions ON and WHERE won't quote columns
			->leftJoin('im_department d', $this->quoteCol('i.department_id').' = '.$this->quoteCol('d.id') )
			->where( $this->quoteCol('i.department_id').' > :val AND '.$this->quoteCol('i.date_outlist').' = :del', array(
				':val' => 0, 
				':del' => '0'
			))
			->queryAll();
	$a = array();
	foreach($rows as $r){
		$a[$r['department_id']] = $r['name'];
	}				
	return $a;
}

As for planned Yii 2.0+ it would be really great thing to supply demos tested for all supported databases.
It would sure uncover some issues related to SQL portability.

Cheers
Lubos

[lubosdz]

Here are examples of the same table defined for all supported PDO databases.
They differ in definition of autoincrement column, adding indexes and quoting column and table names.
It takes some time to figure this out, so here is common example to realize the differences.

mySQL:
======

DROP TABLE IF EXISTS `im_department`;
CREATE TABLE IF NOT EXISTS `im_department` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `code` varchar(20) DEFAULT NULL,
  `description` text,
  `deleted` varchar(1) DEFAULT '0',
  `created_date` int(11) DEFAULT NULL,
  `created_user_id` int(11) DEFAULT NULL,
  `modified_date` int(11) DEFAULT NULL,
  `modified_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Microsoft SQL Server:
=====================

if object_id('dbo.im_department') is not null
begin
  drop table dbo.im_department
end

CREATE TABLE [im_department] (
  [id] int IDENTITY (1,1) NOT NULL,
  [name] varchar(100) NULL,
  [code] varchar(20) NULL,
  [description] text NULL,
  [deleted] varchar(1) DEFAULT '0',
  [created_date] int DEFAULT NULL,
  [created_user_id] int DEFAULT NULL,
  [modified_date] int DEFAULT NULL,
  [modified_user_id] int DEFAULT NULL,
  PRIMARY KEY ([id]),
);

create index deleted on im_department([deleted]);

Oracle
======

drop table "im_department" cascade constraints
/

drop sequence seq_im_department_id
/

create table "im_department" (
  "id" 			number not null,
  "name" 		varchar2(100),
  "code" 		varchar2(20),
  "description" 	varchar2(2000),
  "deleted" 		varchar2(1) default '0',
  "created_date" 	number,
  "created_user_id" 	number,
  "modified_date" 	number,
  "modified_user_id"	number
)
/

alter table "im_department"
add constraint pk_im_department_id primary key ("id")
/

create sequence seq_im_department_id
  increment by 1
  start with 1
  minvalue 1
  maxvalue 999999999999999999999999999
  nocycle
  order
  cache 20
/

create or replace trigger trg_b_i_im_department
 before
  insert
 on "im_department"
referencing new as new old as old
 for each row
begin
     if :new."id" is null then
        select seq_im_department_id.nextval
          into :new."id"
          from dual;
     end if;
end;
/

create index  idx_department_deleted on  "im_department" ("deleted")
/

PostgreSQL
==========

DROP TABLE IF EXISTS "im_department";

CREATE TABLE "im_department" (
  "id" serial,
  "name" varchar(100) DEFAULT NULL,
  "code" varchar(20) DEFAULT NULL,
  "description" text,
  "deleted" varchar(1) DEFAULT '0',
  "created_date" integer DEFAULT NULL,
  "created_user_id" integer DEFAULT NULL,
  "modified_date" integer DEFAULT NULL,
  "modified_user_id" integer DEFAULT NULL,
  CONSTRAINT "pk_department_id" PRIMARY KEY ("id")
);

create index "idx_department_deleted" on "im_department"("deleted");

SQLite (3)
==========

DROP TABLE IF EXISTS "im_department";

CREATE TABLE "im_department" (
  "id" INTEGER PRIMARY KEY,
  "name" varchar(100) ,
  "code" varchar(20) ,
  "description" text,
  "deleted" varchar(1) DEFAULT '0',
  "created_date" integer ,
  "created_user_id" integer ,
  "modified_date" integer ,
  "modified_user_id" integer 
);

create index "idx_department_deleted" on "im_department"("deleted");

[kadishmal]

I am writing the CUBRID Database Driver for Yii, so I have encountered a similar issue. In case of CUBRID, the column names which conflict with reserved words (ex.: type, position, table, name, select, etc.) have to be quoted (either in ", like in Oracle/SQLite/PostgreSQL, or `, like in MySQL, or [], like in MSSQL).

So I have completed the driver development, but when running the test, I try to display $form->dropDownList with elements of a specific column retrieve from the DB table. So here is an SQL, which Yii (not me) generates:

SQL: select * from "tbl_lookup" WHERE type='PostStatus' order by position

Like I said, both type and position are reserved words, so DB generates Syntax error.

Here is how CodeIgniter solves this issue. They allow the Driver to prepare the SQL before executing it. For instance, the following is a snippet from CUBRID Driver for CI.

class CI_DB_cubrid_driver extends CI_DB {
...

function _execute($sql)
{
    $sql - $this->_prep_query($sql);
    return @cubrid_query($sql, $this->conn_id);
}

function _prep_query($sql)
{
    // here you would do escaping or whatever your driver might require.
    return $sql;
}

...
}

Perhaps, in Yii we could have a similar solution.

UPDATE: According to Qiang, this won't be fixed in 1.1 unfortunately.