Cdbcriteria Param Binding

On login page of YII application I have put following condition to validate user:

[sup]$criteria->condition = ‘uname ILIKE :username OR email_id ILIKE :username’;

$criteria->params = array(’:username’ => $this->username);[/sup]

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

[sub]$criteria->compare(‘uname’,$this->username,true,‘OR’);

$criteria->compare(‘email_id’,$this->username,true,‘OR’);[/sub]

But in that case It will build query with "LIKE" not with "ILIKE".

that’s why i had to make proper use of $criteria->condition.

you should rather add slashes (to escape special chars) not remove them…




\ => \\

_ => \_

% => \%



try using simply strtr to correctly escape user input using above schema

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.

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#compare-detail

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addSearchCondition-detail




  $criteria->condition = 'uname ILIKE :username OR email_id ILIKE :username2';

  $keyword = '%' . strtr($this->username, array('%'=>'\%', '_'=>'\_', '\\'=>'\\\\')) . '%';

  $criteria->params = array(':username' => $keyword, ':username2' => $keyword );



Note that we have to use 2 individual place holders for uname and email_id, even if they share the same keyword. :)

[EDIT]

In your particular case, you will want to omit ‘%’ signs:




  $keyword = strtr($this->username, array('%'=>'\%', '_'=>'\_', '\\'=>'\\\\'));



Whoa! And why do we need spearate placeholders if the value is same?? I thought named parameters are meant to be reusable in many places in query…

I have used a wrong emoticon. It should have been :( instead of :)

No, I meant why can’t you do just:




  $criteria->condition = 'uname ILIKE :username OR email_id ILIKE :username';

  $keyword = '%' . strtr($this->username, array('%'=>'\%', '_'=>'\_', '\\'=>'\\\\')) . '%';

  $criteria->params = array(':username' => $keyword );



(only one named parameter)

I think it would work just fine :)

Oh, really?

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.

@redguy

I’ve confirmed the error.

test source




    $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 group t LEFT OUTER JOIN is_users createdBy ON (t.created_by=createdBy.id) LEFT OUTER JOIN is_user_profiles c_profile ON (c_profile.user_id=createdBy.id) LEFT OUTER JOIN is_users updatedBy ON (t.updated_by=updatedBy.id) LEFT OUTER JOIN is_user_profiles u_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 group t LEFT OUTER JOIN is_users createdBy ON (t.created_by=createdBy.id) LEFT OUTER JOIN is_user_profiles c_profile ON (c_profile.user_id=createdBy.id) LEFT OUTER JOIN is_users updatedBy ON (t.updated_by=updatedBy.id) LEFT OUTER JOIN is_user_profiles u_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

Stack trace:

#0 E:\yii-master\framework\db\CDbCommand.php(433): CDbCommand->queryInternal(‘fetchColumn’, 0, Array)

#1 E:\yii-master\framework\db\ar\CActiveFinder.php(764): CDbCommand->queryScalar()

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)…

OK, I think I got it.

Would you please try the following codes?




$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).

I see.

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



Right?




$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 :expressionless:

Thank you so much, redguy.

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.