Yii Framework Forum: Cdbcriteria Param Binding - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Cdbcriteria Param Binding Rate Topic: -----

#1 User is offline   sarvesh 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 09-March 11

Posted 04 July 2014 - 04:19 AM

On login page of YII application I have put following condition to validate user:
$criteria->condition = 'uname ILIKE :username OR email_id ILIKE :username';
$criteria->params = array(':username' => $this->username);

If i put backslah in username text field it throws an error:
CDbCommand failed to execute the SQL statement: SQLSTATE[22025]: Invalid escape sequence: 7 ERROR: LIKE pattern must not end with escape character
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

$criteria->compare('uname',$this->username,true,'OR');
$criteria->compare('email_id',$this->username,true,'OR');


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.
0

#2 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 809
  • Joined: 02-July 10
  • Location:Central Poland

Posted 04 July 2014 - 05:49 AM

you should rather add slashes (to escape special chars) not remove them...
\ => \\
_ => \_
% => \%

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

#3 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 04 July 2014 - 07:35 AM

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.yiiframew...#compare-detail
http://www.yiiframew...ondition-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('%'=>'\%', '_'=>'\_', '\\'=>'\\\\'));

0

#4 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 809
  • Joined: 02-July 10
  • Location:Central Poland

Posted 04 July 2014 - 09:07 AM

View Postsoftark, on 04 July 2014 - 07:35 AM, said:


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


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...
red
0

#5 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 04 July 2014 - 06:24 PM

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

#6 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 809
  • Joined: 02-July 10
  • Location:Central Poland

Posted 05 July 2014 - 03:47 AM

View Postsoftark, on 04 July 2014 - 06:24 PM, said:

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 :)
red
0

#7 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 05 July 2014 - 05:27 AM

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.
0

#8 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 06 July 2014 - 05:52 PM

@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:

Quote

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens


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()
...
0

#9 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 809
  • Joined: 02-July 10
  • Location:Central Poland

Posted 07 July 2014 - 02:50 AM

View Postsoftark, on 06 July 2014 - 05:52 PM, said:

@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)...
red
0

#10 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 07 July 2014 - 07:50 AM

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:

Quote

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.


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.
0

#11 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 809
  • Joined: 02-July 10
  • Location:Central Poland

Posted 07 July 2014 - 09:06 AM

View Postsoftark, on 07 July 2014 - 07:50 AM, said:

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).
red
0

#12 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 07 July 2014 - 05:23 PM

View Postredguy, on 07 July 2014 - 09:06 AM, said:

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?
0

#13 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 809
  • Joined: 02-July 10
  • Location:Central Poland

Posted 08 July 2014 - 02:53 AM

View Postsoftark, on 07 July 2014 - 05:23 PM, said:

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 :|
red
0

#14 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 08 July 2014 - 08:46 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users