Detailed analysis of an SQL injection vulnerability in an earlier version of the ThinkPHP framework

There was an announcement on ThinkPHP official website that there was an SQL injection vulnerability in ThinkPHP 3.1. 3 and before, and the vulnerability existed in ThinkPHP/Lib/Core/Model. class. php file
Explanation of the method of "preventing SQL injection" according to official documents (refer to http://doc.thinkphp.cn/manual/sql_injection. html)
Using query condition preprocessing prevents SQL injection, and yes, it works when using the following code:

$Model->where("id=%d and username='%s' and xx='%f'",array($id,$username,$xx))->select();

Or

$Model->where("id=%d and username='%s' and xx='%f'",$id,$username,$xx)->select();

However, when you use the following code, it has no effect of "preventing SQL injection" (but the official document says it can prevent SQL injection):

$model->query('select * from user where id=%d and status=%s',$id,$status);

Or

$model->query('select * from user where id=%d and status=%s',array($id,$status));

Cause analysis:

The parseSql function in the ThinkPHP/Lib/Core/Model. class. php file does not implement SQL filtering.
Its primitive function is:

protected function parseSql($sql,$parse) {
//  Parse expression 
if(true === $parse) {
  $options = $this->_parseOptions();
  $sql =  $this->db->parseSql($sql,$options);
}elseif(is_array($parse)){ // SQL Pretreatment 
  $sql = vsprintf($sql,$parse);
}else{
  $sql  =  strtr($sql,array('__TABLE__'=>$this->getTableName(),'__PREFIX__'=>C('DB_PREFIX')));
}
$this->db->setModel($this->name);
return $sql;
}

Validation vulnerability (example):
Request address:

http://localhost/Main?id=boo" or 1="1

Or

http://localhost/Main?id=boo%22%20or%201=%221

action code:

$model=M('Peipeidui');
$m=$model->query('select * from peipeidui where name="%s"',$_GET['id']);
dump($m);exit;

Or:

$model=M('Peipeidui');
$m=$model->query('select * from peipeidui where name="%s"',array($_GET['id']));
dump($m);exit;

Results:

Table peipeidui All data is listed, and the SQL injection statement takes effect.

Solution:

You can modify the parseSql function to:

protected function parseSql($sql,$parse) {
//  Parse expression 
if(true === $parse) {
  $options = $this->_parseOptions();
  $sql =  $this->db->parseSql($sql,$options);
}elseif(is_array($parse)){ // SQL Pretreatment 
  $parse = array_map(array($this->db,'escapeString'),$parse);// This behavior adds a new code 
  $sql = vsprintf($sql,$parse);
}else{
  $sql  =  strtr($sql,array('__TABLE__'=>$this->getTableName(),'__PREFIX__'=>C('DB_PREFIX')));
}
$this->db->setModel($this->name);
return $sql;
}

Summary:
1. Don't rely too much on the underlying SQL filtering of TP, programmers should do a good job of security check
2. It is not recommended to use $_ GET and $_ POST directly