If i put backslah in username text field it throws an error:
[sub]CDbCommand failed to execute the SQL statement: SQLSTATE[22025]: Invalid escape sequence: 7 ERROR: LIKE pattern must not end with escape character[/sub]
I know there are many ways in php to remove slashes, I want to know is there any way in YII which will escape invalid character while binding with query.
I can write above condition in different way which will not give any problem
CDbCriteria::compare() is a short-cut method for CDbCriteria::addSearchCondition() and CDbCriteria::addInCondition(). In the source code of CDbCriteria::addSearchConditon(), you could see what you had to do when you wanted to do it on your own using ILIKE.
I had an experience in which that kind of shared place holder gave me an error saying "wrong number of parameters" or something like that. Gonna check it again.
$criteria->condition = 't.name like :keyword OR t.memo like :keyword';
$criteria->params = array(':key' => $this->name);
The code above produces an error, saying:
application.log
2014/07/07 07:45:17 [error] [system.db.CDbCommand] CDbCommand::fetchColumn() がSQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokensで失敗しました。実行されたSQL文は: SELECT COUNT(DISTINCT t.id) FROM groupt LEFT OUTER JOIN is_userscreatedBy ON (t.created_by=createdBy.id) LEFT OUTER JOIN is_user_profilesc_profile ON (c_profile.user_id=createdBy.id) LEFT OUTER JOIN is_usersupdatedBy ON (t.updated_by=updatedBy.id) LEFT OUTER JOIN is_user_profilesu_profile ON (u_profile.user_id=updatedBy.id) WHERE (t.name like :keyword OR t.memo like :keyword)
in E:\isarigami\cgi-bin\is-app\modules\addressBook\views\group\admin.php (165)
in E:\isarigami\cgi-bin\is-app\modules\addressBook\controllers\GroupController.php (171)
in E:\yii-sa-common\protected\modules\rights\components\RController.php (36)
2014/07/07 07:45:17 [error] [exception.CDbException] exception ‘CDbException’ with message ‘CDbCommand はSQL文を実行できません。:SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was: SELECT COUNT(DISTINCT t.id) FROM groupt LEFT OUTER JOIN is_userscreatedBy ON (t.created_by=createdBy.id) LEFT OUTER JOIN is_user_profilesc_profile ON (c_profile.user_id=createdBy.id) LEFT OUTER JOIN is_usersupdatedBy ON (t.updated_by=updatedBy.id) LEFT OUTER JOIN is_user_profilesu_profile ON (u_profile.user_id=updatedBy.id) WHERE (t.name like :keyword OR t.memo like :keyword)’ in E:\yii-master\framework\db\CDbCommand.php:543
Hmm… that is REALLY strange because I also did some tests:
print_r( Yii::app()->db->createCommand( 'SELECT * from user WHERE email LIKE :param OR login LIKE :param' )->queryAll( true, array( ':param'=>'%a%' ) ) );
and run it against Mysql 5.1 and Postgresql 9.3 under PHP 5.4.5 (Windows) and there was no errors at all… just expected result… Can repeat this test on Linux, but I don’t think there will be any difference
what was your environment? I am pretty sure placeholders are reusable because I am using this feature from time to time (for example in some complex date comparisons)…
$param = '%a%';
Yii::app()->db->createCommand('SELECT * from user WHERE email LIKE :param OR login LIKE :param')
->bindParam(':param', $param)
->queryAll()
$param = '%a%';
Yii::app()->db->createCommand('SELECT * from user WHERE email LIKE :param OR login LIKE :param')
->bindValue(':param', $param)
->queryAll()
I believe the former will fail, while the latter will work as expected.
I’ve been looking around in the manual of PDO.
PDO::prepare(),
PDOStatement::execute(),
PDOStatement::bindParam() and
PDOStatement::bindValue().
In the page for PDO::prepare(), they say:
This is because it expects bindParam to be used for binding parameters. Because “bindParam” takes the reference of a variable, it cannot be bound to more than 1 named parameter. But it’s not precisely true when you use bindValue or the input parameter of execute() for parameter binding.
interesting (although it is reasonable that bindParam need 1-1 binding as it is bidirectional bond). but how is it possible that your first example failed (using params in criteria)? AR and CCommandBuilder internally use only bindValue…
ok… found out… funny thing is that for postgres my example works not matter if I set emulatePrepare or not… For MySQL it fails when emulatePrepare is not set (false).
So I was wrong in saying bindVale will permit shared names. Without any reason I thought that CCommandBuilder uses bindParam.
After all, we can summarize like the following:
$sql = 'SELECT * from user WHERE email LIKE :param OR login LIKE :param';
$param = '%a%';
Yii::app()->db->createCommand($sql)->bindParam(':param', $param)->queryAll() // NG
Yii::app()->db->createCommand($sql)->bindValue(':param', $param)->queryAll() // NG
Yii::app()->db->createCommand($sql)->queryAll(true, array(':param' => $param)) // OK
$sql = 'SELECT * from user WHERE email LIKE :param OR login LIKE :param';
$param = '%a%';
Yii::app()->db->createCommand($sql)->bindValue(':param', $param)->queryAll() // NG -> OK
Yii::app()->db->createCommand($sql)->queryAll(true, array(':param' => $param)) // OK
those will work (both) depending on emulatePrepare setting and (apparently) on dbms which may influence emulatePrepare behavior… not an easy case
queryAll (and all CCommandBuilder queries) internally use bindValue, so statements above are equal. We have ‘emulatePrepare=>true’ in our config template, so we can safely use named params multiple times in query, but changing now that switch may cause troubles
I haven’t cared much about CDbConnection::emulatePrepare (and emulation mode of PDO) till now, and it has been left untouched with its default value of false without explicitly defined in my db configuration.