Styjun

My problem is very specific, and I couldn't find any information to help me. Here it is:

I use MySQL mariadb-10.2.3 and CodeIgniter 3, and have a problem in a query because CodeIgniter failed trying to protect against SQL injection:

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

And the original code in my PHP file is:

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

The problem is in this line (in the error message):

`CASE WHEN bdc_valide_par_responsable`.`bdc_id IS NOT NULL THEN uvr`.`id ELSE uvrp`.`id END` AS `responsable_qui_valide_id`,

As you can see, CodeIgniter inserts the small apostrophe ` to prevent SQL injection, but not always. And in this case, the apostrophes are not well inserted and it causes a wrong query.

The strange thing is, when I replace the conditions in the bad CASE by this one:

THEN CONCAT(uvr.id)

or by:

THEN (SELECT uvr.id)

No more syntax error ...

What's happening? Is there a better way to write the query with CodeIgniter?