Clever Geek Handbook

How to use mysql_escape_string () in Yii framework?

As we all know, we cannot use raw MySQL queries in a framework like Yii. I want to use mysql_escape_string

in my project that is running on Yii system to get away from SQL injection in user input.

I know I am mysql_escape_string

deprecated in PHP 5.5 and that I have a PDO alternative. What is the alternative in Yii framework as well as PDO way for mysql_escape_string ()?

+3


source to share


4 answers


An alternative mysql_escape_string

in PDO is to use prepared statements. For example, in Yii:

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


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

You are protected from SQL injection when you pass parameters through placeholders in a prepared statement.

+6


source


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


You can add malicious code in the get parameters.

+3


source


Exiting Query Parameters with Prepared Queries '?' placeholders have their drawbacks (the escaped parameter is removed from the request body making it difficult to work with some queries, perhaps an additional reverse route could be added to the database, which is not always justified; if the query is essentially dynamic, then preparing many of them may actually take up server resources).

Yii has a quoteValue method that can be used to exclude request parameters outside of a prepared request form.

+2


source


You don't need to go out of parameters when using ActiveRecords

.

However, if you want to use this feature mysql_escape_string

, you can try withmysqli_escape_string()

I have used this since Yii

when doing queries with a high degree of complexity that would have performance issues with the models, and I needed to execute SQL queries directly in the DB.

You can use Yii::app()->db->createCommand($sql)->queryAll()

(or any other similar function) for this.

-1


source






More articles:


All Articles