通过yii中的sql注入更新安全性

Question

Could you please tell if these 2 fragments of code secure in yii. Fragent 1:

 $numberOfRows = $this->updateAll(array('full_path' => $target, 'title' => $name,                'machine_name' => $name), 'full_path = :path', array(':path' => $path));

Should I escape $target and $name in this query?

Fragment 2:

$sql = "UPDATE folders";
$sql .= " SET full_path = CONCAT('" . $target . "',SUBSTR(full_path, " . (strlen($path)  + 1) . ", LENGTH(full_path)-1))";
$sql .= " WHERE full_path LIKE '" . $path . "%'";
$command = $this->dbConnection->createCommand($sql);
$command->execute();

Should I escape $target and full_path here using CDbConnection::quoteValue() or something like this in these 2 fragments? I also one how to escape path in the Fragment 2 to avoid issues with special symbols used with LIKE (%, _).

I made changes to fragment 2 using binds and escaping %_:

$sql = "UPDATE folders";
$sql .= " SET full_path = CONCAT(:target, SUBSTR(full_path, " . (strlen($path) + 1) . ", LENGTH(full_path)-1))";
$sql .= " WHERE full_path LIKE  :pathFilter";
$command = $this->dbConnection->createCommand($sql);

//escape %_ that can be used in SQL LIKE expression
$pathFilter = addcslashes($path, '%_') . '%';

$command->bindParam(":pathFilter", $pathFilter, PDO::PARAM_STR);
$command->bindParam(":target", $target, PDO::PARAM_STR);

$command->execute();

Is it correct? Is there a more elegent way to do it?

Answer 1

Speaking of more elegant ways, you can always avoid named parameters, which will dramatically shorten your code:

$sql  = "UPDATE folders SET";
$sql .= " full_path = CONCAT(?, SUBSTR(full_path, ?, LENGTH(full_path)-1))";
$sql .= " WHERE full_path LIKE ?";

//escape %,_ and \ that can be used in SQL LIKE expression
$pathFilter = addcslashes($path, '\%_') . '%'; // I've added a slash here

$command = $this->dbConnection->createCommand($sql);
$command->execute([$target, strlen($path) + 1, $pathFilter]);