更新在 Yii 中免受 SQL 注入的影响 - updates secure from sql injections in yii

你能不能说说这 2 段代码在 yii 中是否安全。Fagent1：

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

我是否应该在此查询中转义$target和$name？

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

我应该在这里逃脱$target和full_path吗CDbConnection：：quoteValue（）或这两个片段中的类似内容？我还有一个如何在片段 2 中转义路径以避免与 LIKE （%， _）一起使用的特殊符号的问题。

我使用绑定和转义 %_ 对片段 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  :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();

正确吗？有没有更优雅的方法呢？

说到更优雅的方式，你总是可以避免命名参数，这将大大缩短你的代码：

$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]);

您有两个选择：

a）使用模型对象，例如，在您的情况下，您可以为文件夹表提供模型类以及要插入/更新数据的任何其他关联表。这将帮助您使用一组模型（如果适合您）。模型类具有内置函数，用于在插入数据库之前验证数据。例如，记录保存方法。进一步阅读此内容以了解相关的安全详细信息

假设您有Folder模型：

 $path='path to be updated';
 $criteria=new CDbCriteria;
  $criteria->compare('full_path',$path,true);
  $folder=Folder::model()->find($criteria);
  if($folder){
   $folder->attributes=$data;
    if($folder->save()){
      echo 'updated successfully';
    }else{
      echo 'invalid data';
    }
  }

b）如果不需要创建模型类，请使用绑定参数详细文章，见#5绑定参数

假设您构造了 $full_path 和 $path 变量

  //$full_path=[construct using php]
  //$path=[construct using php]
  $sql = "UPDATE folders SET full_path = :newPath  WHERE full_path LIKE :oldPath"
  $command = $this->dbConnection->createCommand($sql);
  $command->bindParam(":newPath", $full_path, PDO::PARAM_STR);
  $old_path=addcslashes($path,'%_').'%'; 
  $command->bindParam(":oldPath", $old_path, PDO::PARAM_STR);
  $command->execute();

希望这会有所帮助