Sql Injection Question

Yii 1.1 General Discussion
1

Hi all,

I am writing a secure yii applications and got some problems.


$criteria->addInCondition('id',$_GET['id']);

This code is work for me. But is not safe.




$criteria->addInCondition('id',':id');

$criteria->params = array(':id' => $_GET['id']);

After I use this code, will get an error.


reset() [<a href='function.reset'>function.reset</a>]: Passed variable is not an array or object

How can I mark the code work? Thanks!

Another SQL Injection question


$criteria->addCondition("title LIKE '%".$keywords."%' OR description LIKE '%".$keywords."%'");





$posts = Post::model()->findAll(array('condition'=>"status=1 AND (title LIKE '%".$keywords."%' OR description LIKE '%".$keywords."%')"));

How to rewrite these code to protect the database?

Thanks!

2

For the first question, you’re using the wrong method. The addInCondition() method is used to compare a column (or expression) against several values, so it expects you to provide an array.

First, when fetching data from the $_GET array, use the methods Yii provides, as they will prevent errors if the requested key doesn’t exist:




$id = Yii::app()->request->getQuery('id');

If ‘id’ is your primary key field, you can get the record without using a CDbCriteria object like this:




$model = YourArClass::model()->findByPk($id);

If it’s not your primary key field, you can use this:




$model = YourArClass::model()->findByAttributes(array('id'=>$id));

If you need to use a CDbCriteria object because you have additional conditions that you have to apply, use this form:




$criteria->addColumnCondition(array('id'=>$id));

All of the above examples will safely parameterize the generated query, so they will be safe from SQL injection.

The example code in your second question is wide open to SQL injection. Look into using the addSearchCondition() method when you need to use a LIKE clause. You’ll need to call that method twice, and set the $operator parameter to ‘OR’ for the second one, but watch out for operator precedence issues if you’re combining it with other conditions.

3

Thank you for your reply.

Actually $_GET[‘id’] is an array.

I am using this code, its work, but not safe.


$criteria=new CDbCriteria;

$criteria->addCondition("t.status='1'");

$criteria->addCondition("title LIKE '%".$keywords."%' OR description LIKE '%".$keywords."%'");

$criteria->addInCondition('t.id',$_GET['id']);

The Query:


WHERE (((t.status='1') AND (title LIKE '%keywords%' OR description LIKE '%keywords%')) AND (t.id IN (:ycp1, :ycp2)))

But if I use addSearchCondition.


$criteria=new CDbCriteria;

$criteria->addCondition("t.status='1'");

$criteria->addSearchCondition($title, $keywords, true, 'OR');

$criteria->addSearchCondition($description, $keywords, true, 'OR');

$criteria->addInCondition('t.id',$_GET['id']);

The query will become this and got the wrong result.


WHERE ((((t.status='1') OR (title LIKE :ycp0)) OR (description LIKE :ycp1)) AND (t.id IN (:ycp2, :ycp3)))

How to correct the query?? Thanks.

4

Anyone can help me?? Thanks!!