How to use mysql_escape_string() in Yii framework?

Question

As we all know, we cannot use raw MySQL queries in frameworks such as Yii. I want to use mysql_escape_string in my project which runs in Yii framework to get away from SQL injection in user input.

I am aware that mysql_escape_string is deprecated in PHP 5.5 and that I have a PDO alternative. What is the alternative in Yii framework and also the PDO way of mysql_escape_string()?

Answer 1

You don't need to escape parameters if using ActiveRecords.

However, if you want to use that mysql_escape_string function you can try with mysqli_escape_string()

I've used this with Yii when executing high complexity queries that would have had performance issues if working with models and I needed to execute SQL queries directly to the DB.

For doing this, you can use Yii::app()->db->createCommand($sql)->queryAll() (or any other similar function).

Answer 2

It is best to use prepared statements for automatic parameter escaping. However, this method of CDbConnection should also do the trick:

Yii::app()->db->quoteValue($your_value);

In essence this quotes a string value for use in a query, it is a wrapper of PDO::quote().

Read more here.

Answer 3

Use CHTMlPurifier:

// Example
$p = new CHtmlPurifier();

$user = Yii::app()->db->createCommand()
    ->select('username, password')
    ->from('tbl_user')
    ->where('id=:id', array(':id'=>$p->purify($_GET['userId']);))
    ->queryRow();

It is possible add malicious code in get parameters.

Answer 4

Escaping the query parameters using the prepared queries '?' placeholders has its drawbacks (the escaped parameter is moved away from the body of the query, thus making some queries harder to work with; there might be an additional roundtrip to the database which isn't always justified; if the query is essentially dynamic then preparing lots of them might actually take server resources).

Yii has the quoteValue method which can be used to escape the query parameters outside of the prepared query form.

Answer 5

The alternative to mysql_escape_string in PDO is using prepared statements. In Yii for example:

$user = Yii::app()->db->createCommand()
    ->select('username, password')
    ->from('tbl_user')
    ->where('id=:id', array(':id'=>$_GET['userId']))
    ->queryRow();

(From the Yii reference documentation http://www.yiiframework.com/doc/api/1.1/CDbCommand)

You are secured you against SQL injection when you pass parameters through placeholders in a prepared statement.