CODEWITHSUNDEEP
phpmysqlyiisql-injection
Bala
Bala

Reputation: 3638

How to use mysql_escape_string() in Yii framework?

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()?

Upvotes: 6

Views: 13697

Answers (5)

Bud Damyanov
Bud Damyanov

Reputation: 31879

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.

Upvotes: 5

Nico Savini
Nico Savini

Reputation: 487

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

Upvotes: 0

ArtemGr
ArtemGr

Reputation: 12547

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.

Upvotes: 3

Joni
Joni

Reputation: 111329

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.

Upvotes: 10

FabianoLothor
FabianoLothor

Reputation: 2985

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.

Upvotes: 0

Related Questions