mysql - 为什么 CodeIgniter 在我使用 CASE 时会错误地转义查询的某些元素？
问题描述
我的问题非常具体，我找不到任何信息来帮助我。这里是：
我使用 MySQL mariadb-10.2.3 和 CodeIgniter 3，并且在查询中遇到问题，因为 CodeIgniter 尝试防止 SQL 注入失败：
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`id END` AS `responsable_qui_valide_id`, CASE WHEN bdc_valide_par_responsable.b' at line 13
SELECT `b`.`id`, CONCAT(u.nom,' ',u.prenom) AS demandeur,
CONCAT(u.email_nom,'@',e.domaine) AS demandeur_email,
DATE_FORMAT(b.horodateur, "%d/%m/%Y") AS horodateur,
`p`.`nom` AS `direction_concernee`,
`b`.`expression_besoin` AS `expression`,
CASE
WHEN oa.bdc_id IS NOT NULL
THEN oa.explication
ELSE
CASE
WHEN oi.bdc_id IS NOT NULL
THEN CONCAT('Demande Interservices - Dossier n°',oi.demande_interservices_id,' de ',CONCAT(uis.nom,' ',uis.prenom))
ELSE 'Entretien ou besoin courant'
END
END AS origine,
CONCAT(u_elu.nom,' ',u_elu.prenom) AS elu_associe_au_bdc,
`bdc_code`.`code_famille`,
`bdc_code`.`imputation`,
`d`.`id` AS `num_devis_choisi`,
`d`.`fournisseur` AS `fournisseur_devis_choisi`,
`d`.`devis`, CONCAT(d.montant,'€') AS montant_devis_choisi,
CASE d.marche
WHEN 1
THEN '(Marché)'
ELSE NULL
END AS marche, `bdc_devis_choisi`.`raison_choix` AS `choix`,
`bdc_niveau_validation`.`niveau`,
CASE
WHEN bdc_refuse.bdc_id IS NULL
THEN CAST(0 AS BINARY)
ELSE CAST(1 AS BINARY)
END AS refus,
`bdc_refuse`.`raison_refus`,
CONCAT(ur.nom,' ',ur.prenom) AS refuseur,
`ur`.`id` AS `refuseur_id`,
CONCAT(uvr.email_nom,'@',e_uvr.domaine) AS refuseur_email,
CASE
WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN CONCAT(uvr.nom,' ',uvr.prenom)
ELSE CONCAT(uvrp.nom,' ',uvrp.prenom)
END AS responsable_qui_valide,
`CASE WHEN bdc_valide_par_responsable`.`bdc_id IS NOT NULL THEN uvr`.`id ELSE uvrp`.`id END` AS `responsable_qui_valide_id`,
CASE
WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN CONCAT(uvr.email_nom,'@',e_uvr.domaine)
ELSE CONCAT(uvrp.email_nom,'@',e_uvrp.domaine)
END AS responsable_email,
CASE
WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END AS responsable_a_valide,
CASE
WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_responsable.date_validation, "%d/%m/%Y")
ELSE NULL END AS date_validation_responsable,
CASE
WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN CONCAT(uve.nom,' ',uve.prenom)
ELSE CONCAT(u_elu.nom,' ',u_elu.prenom)
END AS elu_qui_valide,
CASE
WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN CONCAT(uve.email_nom,'@',e_uve.domaine)
ELSE CONCAT(u_elu.email_nom,'@',e_u_elu.domaine)
END AS elu_email,
CASE
WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END AS elu_a_valide,
CASE
WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_elu.date_validation, "%d/%m/%Y")
ELSE NULL
END AS date_validation_elu,
CASE
WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN CONCAT(uvc.nom,' ',uvc.prenom)
ELSE
(SELECT CONCAT(urc.nom,' ',urc.prenom)
FROM utilisateur AS urc
JOIN pole ON pole.responsable_id = urc.id
WHERE pole.nom LIKE 'Pôle Ressources')
END AS compta_qui_valide,
CASE
WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN CONCAT(uvc.email_nom,'@',e_uvc.domaine)
ELSE
(SELECT CONCAT(urc.email_nom,'@',e_urc.domaine)
FROM utilisateur AS urc
JOIN pole ON pole.responsable_id = urc.id
JOIN email_domaine AS e_urc ON urc.email_domaine_id = e_urc.id
WHERE pole.nom LIKE 'Pôle Ressources')
END AS compta_email,
CASE
WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END AS compta_a_valide,
CASE
WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_comptabilite.date_validation, "%d/%m/%Y")
ELSE NULL
END AS date_validation_compta,
CASE
WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END AS dirigeant_a_valide,
CASE
WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN CONCAT(uvd.nom,' ',uvd.prenom)
ELSE NULL
END AS dirigeant_qui_a_valide,
CASE
WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN CONCAT(uvd.email_nom,'@',e_uvd.domaine)
ELSE NULL
END AS dirigeant_email,
CASE
WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_dirigeant.date_validation, "%d/%m/%Y")
ELSE NULL
END AS date_validation_dirigeant
FROM `bon_de_commande` AS `b`
JOIN `utilisateur` AS `u` ON `b`.`demandeur_id` = `u`.`id`
JOIN `email_domaine` AS `e` ON `u`.`email_domaine_id` = `e`.`id`
JOIN `pole` AS `p` ON `b`.`pole_concerne_id` = `p`.`id`
LEFT JOIN `bdc_origine_besoin_courant` AS `ob` ON `ob`.`bdc_id` = `b`.`id`
LEFT JOIN `bdc_origine_interservices` AS `oi` ON `oi`.`bdc_id` = `b`.`id`
LEFT JOIN `demande_interservices` ON `oi`.`demande_interservices_id` = `demande_interservices`.`id`
LEFT JOIN `utilisateur` AS `uis` ON `demande_interservices`.`utilisateur_id` = `uis`.`id`
LEFT JOIN `bdc_origine_autre` AS `oa` ON `oa`.`bdc_id` = `b`.`id`
LEFT JOIN `bdc_associe_elu` ON `bdc_associe_elu`.`bdc_id` = `b`.`id`
LEFT JOIN `utilisateur` AS `u_elu` ON `bdc_associe_elu`.`elu_id` = `u_elu`.`id`
LEFT JOIN `email_domaine` AS `e_u_elu` ON `u_elu`.`email_domaine_id` = `e_u_elu`.`id`
LEFT JOIN `bdc_code` ON `bdc_code`.`bdc_id` = `b`.`id`
JOIN `bdc_devis_choisi` ON `bdc_devis_choisi`.`bdc_id` = `b`.`id`
JOIN `devis` AS `d` ON `bdc_devis_choisi`.`devis_id` = `d`.`id`
JOIN `bdc_niveau_validation` ON `bdc_niveau_validation`.`bdc_id` = `b`.`id`
LEFT JOIN `bdc_refuse` ON `bdc_refuse`.`bdc_id` = `b`.`id`
LEFT JOIN `bdc_valide_par_commission` ON `bdc_valide_par_commission`.`bdc_id` = `b`.`id`
LEFT JOIN `utilisateur` AS `uvcm` ON `bdc_valide_par_commission`.`elu_id` = `uvcm`.`id`
LEFT JOIN `bdc_valide_par_comptabilite` ON `bdc_valide_par_comptabilite`.`bdc_id` = `b`.`id`
LEFT JOIN `utilisateur` AS `uvc` ON `bdc_valide_par_comptabilite`.`responsable_id` = `uvc`.`id`
LEFT JOIN `email_domaine` AS `e_uvc` ON `uvc`.`email_domaine_id` = `e_uvc`.`id`
LEFT JOIN `bdc_valide_par_elu` ON `bdc_valide_par_elu`.`bdc_id` = `b`.`id`
LEFT JOIN `utilisateur` AS `uve` ON `bdc_valide_par_elu`.`elu_id` = `uve`.`id`
LEFT JOIN `email_domaine` AS `e_uve` ON `uve`.`email_domaine_id` = `e_uve`.`id`
LEFT JOIN `bdc_valide_par_responsable` ON `bdc_valide_par_responsable`.`bdc_id` = `b`.`id`
LEFT JOIN `utilisateur` AS `uvr` ON `bdc_valide_par_responsable`.`responsable_id` = `uvr`.`id`
LEFT JOIN `email_domaine` AS `e_uvr` ON `uvr`.`email_domaine_id` = `e_uvr`.`id`
LEFT JOIN `utilisateur` AS `uvrp` ON `p`.`responsable_id` = `uvrp`.`id`
LEFT JOIN `email_domaine` AS `e_uvrp` ON `uvrp`.`email_domaine_id` = `e_uvrp`.`id`
LEFT JOIN `bdc_valide_par_dirigeant` ON `bdc_valide_par_dirigeant`.`bdc_id` = `b`.`id`
LEFT JOIN `utilisateur` AS `uvd` ON `bdc_valide_par_dirigeant`.`dirigeant_id` = `uvd`.`id`
LEFT JOIN `email_domaine` AS `e_uvd` ON `uvd`.`email_domaine_id` = `e_uvd`.`id`
LEFT JOIN `utilisateur` AS `ur` ON `bdc_refuse`.`refuseur_id` = `ur`.`id`
LEFT JOIN `email_domaine` AS `e_ur` ON `ur`.`email_domaine_id` = `e_ur`.`id`
WHERE `b`.`id` = 1
我的 PHP 文件中的原始代码是：
$select = array(
'b.id',
'CONCAT(u.nom,\' \',u.prenom) AS demandeur',
'CONCAT(u.email_nom,\'@\',e.domaine) AS demandeur_email',
'DATE_FORMAT(b.horodateur, "%d/%m/%Y") AS horodateur',
'p.nom AS direction_concernee',
'b.expression_besoin AS expression',
'CASE WHEN oa.bdc_id IS NOT NULL
THEN oa.explication
ELSE
CASE WHEN oi.bdc_id IS NOT NULL
THEN CONCAT(\'Demande Interservices - Dossier n°\',oi.demande_interservices_id,\' de \',CONCAT(uis.nom,\' \',uis.prenom))
ELSE \'Entretien ou besoin courant\'
END
END
AS origine',
'CONCAT(u_elu.nom,\' \',u_elu.prenom) AS elu_associe_au_bdc',
'bdc_code.code_famille',
'bdc_code.imputation',
'd.id AS num_devis_choisi',
'd.fournisseur AS fournisseur_devis_choisi',
'd.devis',
'CONCAT(d.montant,\'€\') AS montant_devis_choisi',
'CASE d.marche WHEN 1 THEN \'(Marché)\' ELSE NULL END AS marche',
'bdc_devis_choisi.raison_choix AS choix',
'bdc_niveau_validation.niveau',
'CASE WHEN bdc_refuse.bdc_id IS NULL THEN CAST(0 AS BINARY) ELSE CAST(1 AS BINARY) END AS refus',
'bdc_refuse.raison_refus',
'CONCAT(ur.nom,\' \',ur.prenom) AS refuseur',
'ur.id AS refuseur_id',
'CONCAT(uvr.email_nom,\'@\',e_uvr.domaine) AS refuseur_email',
'CASE WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN CONCAT(uvr.nom,\' \',uvr.prenom)
ELSE CONCAT(uvrp.nom,\' \',uvrp.prenom)
END
AS responsable_qui_valide',
'CASE WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN uvr.id
ELSE uvrp.id
END
AS responsable_qui_valide_id',
'CASE WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN CONCAT(uvr.email_nom,\'@\',e_uvr.domaine)
ELSE CONCAT(uvrp.email_nom,\'@\',e_uvrp.domaine)
END
AS responsable_email',
'CASE WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END
AS responsable_a_valide',
'CASE WHEN bdc_valide_par_responsable.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_responsable.date_validation, "%d/%m/%Y")
ELSE NULL
END
AS date_validation_responsable',
'CASE WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN CONCAT(uve.nom,\' \',uve.prenom)
ELSE CONCAT(u_elu.nom,\' \',u_elu.prenom)
END
AS elu_qui_valide',
'CASE WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN (SELECT uve.id)
ELSE (SELECT u_elu.id)
END
AS elu_qui_valide_id',
'CASE WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN CONCAT(uve.email_nom,\'@\',e_uve.domaine)
ELSE CONCAT(u_elu.email_nom,\'@\',e_u_elu.domaine)
END
AS elu_email',
'CASE WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END
AS elu_a_valide',
'CASE WHEN bdc_valide_par_elu.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_elu.date_validation, "%d/%m/%Y")
ELSE NULL
END
AS date_validation_elu',
'CASE WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN CONCAT(uvc.nom,\' \',uvc.prenom)
ELSE
(SELECT CONCAT(urc.nom,\' \',urc.prenom) FROM utilisateur AS urc JOIN pole ON pole.responsable_id = urc.id WHERE pole.nom LIKE \'Pôle Ressources\')
END
AS compta_qui_valide',
'CASE WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN (SELECT uvc.id)
ELSE
(SELECT urc.id FROM utilisateur AS urc JOIN pole ON pole.responsable_id = urc.id WHERE pole.nom LIKE \'Pôle Ressources\')
END
AS compta_qui_valide_id',
'CASE WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN CONCAT(uvc.email_nom,\'@\',e_uvc.domaine)
ELSE
(SELECT CONCAT(urc.email_nom,\'@\',e_urc.domaine) FROM utilisateur AS urc
JOIN pole ON pole.responsable_id = urc.id
JOIN email_domaine AS e_urc ON urc.email_domaine_id = e_urc.id
WHERE pole.nom LIKE \'Pôle Ressources\')
END
AS compta_email',
'CASE WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END
AS compta_a_valide',
'CASE WHEN bdc_valide_par_comptabilite.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_comptabilite.date_validation, "%d/%m/%Y")
ELSE NULL
END
AS date_validation_compta',
'CASE WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN CAST(1 AS BINARY)
ELSE CAST(0 AS BINARY)
END
AS dirigeant_a_valide',
'CASE WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN CONCAT(uvd.nom,\' \',uvd.prenom)
ELSE NULL
END
AS dirigeant_qui_a_valide',
'CASE WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN (SELECT uvd.id)
ELSE NULL
END
AS dirigeant_qui_a_valide_id',
'CASE WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN CONCAT(uvd.email_nom,\'@\',e_uvd.domaine)
ELSE NULL
END
AS dirigeant_email',
'CASE WHEN bdc_valide_par_dirigeant.bdc_id IS NOT NULL
THEN DATE_FORMAT(bdc_valide_par_dirigeant.date_validation, "%d/%m/%Y")
ELSE NULL
END
AS date_validation_dirigeant'
);
$where = array('b.id' => $bdc_id);
$this->db->select($select);
$this->db->from('bon_de_commande AS b');
$this->db->join('utilisateur AS u', 'b.demandeur_id = u.id');
$this->db->join('email_domaine AS e', 'u.email_domaine_id = e.id');
$this->db->join('pole AS p', 'b.pole_concerne_id = p.id');
$this->db->join('bdc_origine_besoin_courant AS ob', 'ob.bdc_id = b.id', 'left');
$this->db->join('bdc_origine_interservices AS oi', 'oi.bdc_id = b.id', 'left');
$this->db->join('demande_interservices', 'oi.demande_interservices_id = demande_interservices.id', 'left');
$this->db->join('utilisateur AS uis', 'demande_interservices.utilisateur_id = uis.id', 'left');
$this->db->join('bdc_origine_autre AS oa', 'oa.bdc_id = b.id', 'left');
$this->db->join('bdc_associe_elu', 'bdc_associe_elu.bdc_id = b.id', 'left');
$this->db->join('utilisateur AS u_elu', 'bdc_associe_elu.elu_id = u_elu.id', 'left');
$this->db->join('email_domaine AS e_u_elu', 'u_elu.email_domaine_id = e_u_elu.id', 'left');
$this->db->join('bdc_code', 'bdc_code.bdc_id = b.id', 'left');
$this->db->join('bdc_devis_choisi', 'bdc_devis_choisi.bdc_id = b.id');
$this->db->join('devis AS d', 'bdc_devis_choisi.devis_id = d.id');
$this->db->join('bdc_niveau_validation', 'bdc_niveau_validation.bdc_id = b.id');
$this->db->join('bdc_refuse', 'bdc_refuse.bdc_id = b.id', 'left');
$this->db->join('bdc_valide_par_commission', 'bdc_valide_par_commission.bdc_id = b.id', 'left');
$this->db->join('utilisateur AS uvcm', 'bdc_valide_par_commission.elu_id = uvcm.id', 'left');
$this->db->join('bdc_valide_par_comptabilite', 'bdc_valide_par_comptabilite.bdc_id = b.id', 'left');
$this->db->join('utilisateur AS uvc', 'bdc_valide_par_comptabilite.responsable_id = uvc.id', 'left');
$this->db->join('email_domaine AS e_uvc', 'uvc.email_domaine_id = e_uvc.id', 'left');
$this->db->join('bdc_valide_par_elu', 'bdc_valide_par_elu.bdc_id = b.id', 'left');
$this->db->join('utilisateur AS uve', 'bdc_valide_par_elu.elu_id = uve.id', 'left');
$this->db->join('email_domaine AS e_uve', 'uve.email_domaine_id = e_uve.id', 'left');
$this->db->join('bdc_valide_par_responsable', 'bdc_valide_par_responsable.bdc_id = b.id', 'left');
$this->db->join('utilisateur AS uvr', 'bdc_valide_par_responsable.responsable_id = uvr.id', 'left');
$this->db->join('email_domaine AS e_uvr', 'uvr.email_domaine_id = e_uvr.id', 'left');
$this->db->join('utilisateur AS uvrp', 'p.responsable_id = uvrp.id', 'left');
$this->db->join('email_domaine AS e_uvrp', 'uvrp.email_domaine_id = e_uvrp.id', 'left');
$this->db->join('bdc_valide_par_dirigeant', 'bdc_valide_par_dirigeant.bdc_id = b.id', 'left');
$this->db->join('utilisateur AS uvd', 'bdc_valide_par_dirigeant.dirigeant_id = uvd.id', 'left');
$this->db->join('email_domaine AS e_uvd', 'uvd.email_domaine_id = e_uvd.id', 'left');
$this->db->join('utilisateur AS ur', 'bdc_refuse.refuseur_id = ur.id', 'left');
$this->db->join('email_domaine AS e_ur', 'ur.email_domaine_id = e_ur.id', 'left');
$this->db->where($where);
$query = $this->db->get();
问题出在这一行（在错误消息中）：
`CASE WHEN bdc_valide_par_responsable`.`bdc_id IS NOT NULL THEN uvr`.`id ELSE uvrp`.`id END` AS `responsable_qui_valide_id`,
如您所见，CodeIgniter 插入小撇号 ` 以防止 SQL 注入，但并非总是如此。在这种情况下，撇号没有很好地插入，它会导致错误的查询。
奇怪的是，当我用这个替换坏 CASE 中的条件时：
THEN CONCAT(uvr.id)
或通过：
THEN (SELECT uvr.id)
没有更多的语法错误...
发生了什么？有没有更好的方法来使用 CodeIgniter 编写查询？
解决方案
推荐阅读
- python - Django Annotated Query 以计算反向关系中使用的所有实体
- reactjs - 使用 useState 和 || 响应不正确的更新 操作员
- python - 如何在python中用0填充分钟时间序列数据？
- c++ - 为什么这个函数标记为noexcept，但可以抛出？
- c++ - 如何从基类向量中获取派生类的名称
- java - 如何在eclipse中将jsp文件链接到另一个jsp？
- entity-framework - 2 个额外的表格是否比一个带有 meta 的表格更好？
- c# - Azure Key Vault：AADSTS700024：.NET Core，Linux 容器。客户端断言不在其有效时间范围内
- reactjs - 为什么启用 CORS 后仍然出现 CORS 错误
- bash - 使用 grep 匹配尖括号内的模式，忽略空格