代码点火器使用子查询将选择的输出传递给另一个选择的IN子句

Question

我想利用Codeigniter中活动记录的自我安全性。

要求是：

1. 根据一些标准从第一个表中获取一些序列号
2. 然后，根据一些复杂的标准，检查第二个表中存在哪些序列号
3. 最后显示第一个表中的结果，这些结果不存在于第二个表的结果集中

我在CI中有以下代码：

$this->db->select('sno');
$this->db->from('table1');
$this->db->where("cid",$cid);
$subquery=$this->db->get_compiled_select();
$this->db->select("pid")->from("table2");
$this->db->group_start();
$this->db->group_start();
$this->db->where("col1 <",$cin);
$this->db->where("col2 >",$cin);
$this->db->group_end();
$this->db->or_group_start();
$this->db->where("col1 <",$cout);
$this->db->where("col2 >",$cout);
$this->db->group_end();
$this->db->or_group_start();
$this->db->where("col1 >",$cin);
$this->db->where("col1 <",$cout);
$this->db->group_end();
$this->db->or_group_start();
$this->db->where("col1",$cin);
$this->db->or_where("col2",$cout);
$this->db->or_where("col1",$cout);
$this->db->or_where("col2",$cin);
$this->db->group_end();
$this->db->or_group_start();
$this->db->where("col2 >",$cin);
$this->db->where("col2 <",$cout);
$this->db->group_end();
$this->db->group_end();
$this->db->where_in("col3",$subquery);
$sbquery = $this->db->get_compiled_select();
$this->db->from('table1');
$this->db->where("tid",$cid);
$this->db->where_not_in("cid",$sbquery);
$result=$this->db->get_compiled_select();
var_dump($result);
exit;

此代码显示以下查询(作为var_dump的结果(：

SELECT * FROM `table1` WHERE `tid` = '6' AND `cid` NOT IN('SELECT `pid`nFROM `table2`nWHERE (n (n`col1` < '2018-12-10'nAND `col2` > '2018-12-10'n )nOR (n`col1` < '2018-12-16'nAND `col2` > '2018-12-16'n )nOR (n`col1` > '2018-12-10'nAND `col1` < '2018-12-16'n )nOR (n`col1` = '2018-12-10'nOR `col2` = '2018-12-16'nOR `col1` = '2018-12-16'nOR `col2` = '2018-12-10'n )nOR (n`col2` > '2018-12-10'nAND `col2` < '2018-12-16'n )n )nAND `col3` IN('SELECT `sno`\nFROM `table1`\nWHERE `cid` = \'6\'')')

当我使用以下代码(最后3行代码(时，这会给出错误的结果：

$result=$this->db->get()->result();
var_dump($result);
exit;

如果我在SQL中直接命中同一个查询，那么我会得到同样错误的结果。

但是，如果我删除斜杠、单引号、新行和其他特殊字符，直接在sql中命中重写的查询，我会得到完全准确的结果。

SELECT * FROM `table1` WHERE `tid` = '6' AND `cid` NOT IN(SELECT `pid` FROM `table2` WHERE ((`col1` < '2018-12-10' AND `col2` > '2018-12-10') OR (`col1` < '2018-12-16' AND `col2` > '2018-12-16') OR (`col1` > '2018-12-10' AND `col1` < '2018-12-16') OR (`col1` = '2018-12-10' OR `col2` = '2018-12-16' OR `col1` = '2018-12-16' OR `col2` = '2018-12-10') OR (`col2` > '2018-12-10' AND `col2` < '2018-12-16')) AND `col3` IN(SELECT `sno` FROM `table1` WHERE `cid` = '6'))

请提出解决方案。

Answer 1

在活动记录子查询中添加FALSE作为第三个参数，这样语句就不会被转义
更改自：

$this->db->where_in("col3",$subquery);
...
$this->db->where_not_in("cid",$sbquery);

至：

$this->db->where_in("col3",$subquery,FALSE);
...
$this->db->where_not_in("cid",$sbquery,FALSE);