Codeigniter更新了SQL问题

Question

从codeigniter 3.1.0更新到3.1.9后，当前网站的一个模块出现了一些问题，查看它输出的SQL，这可能是因为order_by部分，但我可以从SQL错误中看到，添加了几个到多个'，但不知道如何修复此输出。。

代码块出现问题：

interface IssueFlags
{
const FLAG_NONE       = 0x000;
const FLAG_PINNED     = 0x001;        // issue is pinned, will always stick to the top of other issues
const FLAG_PRIVATE    = 0x002;        // issue is limited only to staff and are not visible for others
const FLAG_LOCKED     = 0x004;        // issue is locked and is not editable by anyone, only staff are allowed to edit
const FLAG_CLOSED     = 0x008;        // issue is not opened to comments anymore, only staff are allowed to comment
}
public function get($id = false, $limit = false)
{
if($id = array_filter((array)$id, 'is_numeric'))
$this->db->where_in('bugtracker_issues.id', $id);
if(is_numeric($limit) && !empty($limit))
$this->db->limit($limit);
elseif(is_array($limit) && count($limit) == 2)
$this->db->limit($limit[0], $limit[1]);
// Limit columns to get proper data and prevent duplicate columns name overwrite eachother
$this->db->select('bugtracker_issues.*, account_data.nickname');
// Also join a few columns of account data, so we won't need to query for each user too
$this->db->join('account_data', 'bugtracker_issues.author = account_data.id', 'left');
// This allow us to use functions when bulding our query
$this->db->protect_identifiers = false;
// Sort issues by update then created timestamp, at the end their id and descending, pinned ones always comes first
$this->db->order_by('`flags` & ' . self::FLAG_PINNED . ' DESC, IFNULL(`update`, `create`) DESC, `id` DESC');
$this->db->protect_identifiers = true; // turn it off, as its default state is disabled
$query = $this->db->from('bugtracker_issues')->get();
if(!$query || !is_object($query))
return false;
if(!$query->num_rows())
return array();
return $query->result_array();
}

正在发生的错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 DESC, IFNULL(`update`, `create`) DESC, `id` DESC LIMIT 20' at line 4
SELECT `bugtracker_issues`.*, `account_data`.`nickname` FROM `bugtracker_issues` LEFT JOIN `account_data` ON `bugtracker_issues`.`author` = `account_data`.`id` ORDER BY `flags`` &` 1 DESC, IFNULL(`update`, `create`) DESC, `id` DESC LIMIT 20

Answer 1

您的ORDER BY子句无效。

ORDER BY `flags`` &` 1 DESC, IFNULL(`update`, `create`) DESC, `id` DESC LIMIT 20

这在SQL中没有意义。

您需要修复PHP代码的这一部分，以生成正确的ORDER BY子句：

$this->db->order_by('`flags` & ' . self::FLAG_PINNED . ' DESC, IFNULL(`update`, `create`) DESC, `id` DESC');

应写成：

$this->db->order_by('`flags` DESC, IFNULL(`update`, `create`) DESC, `id` DESC');