Fix SQL Injection (Legacy & Modern) in Yii

SQLi in Yii frameworks typically occurs when developers bypass the built-in abstraction layers in favor of raw string concatenation. Whether you are dealing with a legacy Yii 1.1 CActiveRecord implementation or a modern Yii 2/3 DAO (Database Access Object) layer, the vulnerability exists when user-supplied input is treated as part of the SQL command structure rather than literal data.

The Vulnerable Pattern

// Legacy Yii 1.1 - Raw DAO query
$user = Yii::app()->db->createCommand("SELECT * FROM users WHERE id = " . $_GET['id'])->queryRow();
// Modern Yii 2 - Dangerous Query Builder usage
$status = $_POST[‘status’];
$models = Post::find()->where(“status = $status”)->all();
// Modern Yii 2 - Vulnerable findBySql
$data = User::findBySql(“SELECT * FROM users WHERE username = ’” . $username . ”’”)->all();

The Secure Implementation

The primary defense against SQLi in Yii is the use of Parameterized Queries via PDO. In Yii 1.1, developers must manually call `bindValue()` or `bindParam()` when using `createCommand()`. In Yii 2 and 3, the Query Builder provides a 'Hash Format' (passing an array to `where()`) which is the most secure method as it triggers automatic escaping and binding. When complex logic requires raw SQL fragments, placeholders (e.g., `:id`) must be used in conjunction with a params array. Never pass unsanitized variables directly into `orderBy()`, `groupBy()`, or `having()` methods, as these are often overlooked injection points that Query Builder might not automatically parameterize depending on the specific version and driver.

// Legacy Yii 1.1 - Use Parameter Binding
$user = Yii::app()->db->createCommand('SELECT * FROM users WHERE id = :id')
    ->bindValue(':id', $_GET['id'])
    ->queryRow();
// Modern Yii 2 - Use Hash Format (Recommended)
// This automatically handles quoting and parameter binding
$models = Post::find()->where([‘status’ => $status])->all();
// Modern Yii 2 - Complex queries with manual placeholders
$models = Post::find()
->where([’>’, ‘created_at’, $timestamp])
->andWhere(‘author_id = :authorId’, [‘:authorId’ => $id])
->all();