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