Yii Active Record Sql Injection

Hi. I’m quite new at Yii and I looked at a lot of forum topics here and read the documentation, but I couldn’t find a clear answer about the prevention of SQL injection when using Active Record.

So if we have this code:




$activation_code = $_GET['activation_code'];

$user = User::model()->findByAttributes(array('activation_code'=>$activation_code));



Does Yii escapes the parameters if we use Active Record like this? What about the other find active record methods?

If we have save method and we use this:




$model = new User("register");

$model->name = $_POST[name];

$model->save();



Is this safe? I guess that if we use $model->attributes = $_POST[‘User’]; then it’s safe, but what about that case?

Thanks,

Andrej

The security is build it. In the Working with database page, it says

OK, but what is the difference if I use findByAttributes or findAllByAttributes like I did in my example above and if I bind the parameters like columnNameInDb = :someVarName? In my example there is less code and is more readable. That’s what is confusing to me, I thought maybe my example is prone to SQL Injection.

Thanks,

Andrej

Hi Andrej,

I agree with you that the documentation is a little too short on SQL injection prevention.

The most basic methods are CActiveRecord::find() and CActiveRecord::findAll(), where you can specify a raw SQL clause for $condition parameter like this:




$user = User::model()->find('activation_code='.$_GET['activation_code']);



Yii doesn’t warn you and it will work as expected, but it’s unsafe.

We have to use the parameter binding:




$user = User::model()->find('activation_code=:ac', array(':ac'=>$_GET['activation_code']));



Please read the reference of CActiveRecord::find() carefully.

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#find-detail

In all other CActiveRecord::findXXX() methods, Yii will try to use the 2nd syntax internally.

So you are safe as long as you use those methods in the way they are expected to be used.

But you can still use a raw SQL clause as $condition parameter for those methods. You have to use $params parameter whenever it is necessary.

The 2nd one will go through the model validation, while the 1st one will not … that’s right.

But the both of them are equally safe in the SQL context, because the attributes (e.g. ‘name’) will be properly escaped before saving. Note that the attributes are not safe as they are. They can contain a dangerous SQL string even after they passed the model attribute validation.

So, as I understand it, if we use model()->find, we have to use the parameter binding.

But if we use the other findXXX methods and if we supply the condition as array




$activation_code = $_GET['activation_code'];

$user = User::model()->findByAttributes(array('activation_code'=>$activation_code));



then it’s all safe?

I don’t care for model validation in this case, since the ‘name’ parameter can be any string with any length, so it can contain dangerous SQL string in it. But both of them are safe from SQL injection, no matter if I use it like $model->attributes = $_POST[‘User’]; or if use it like $model->name = $_POST[name];?

I like Yii a lot, but I think some of the things regarding security (sql injection, xss…) are not well documented or are a little confusing.

Thanks,

Andrej

There’s a wiki article on the security. It’s a good supplement to the definitive guide.

http://www.yiiframework.com/wiki/275/how-to-write-secure-yii-applications