[FIX] mail: fix blacklist mechanism

Several quick fixes:

- repair malformed SQL queries and injection problems
- proper token comparisons (consteq)
- better translatable terms (less context-lessplaceholders)
- basic testing of case-(in)sensitivity of blacklist
- safety check for non-implemented cases in search methods for computed
  field

More testing and review is needed for case-sensitivity and email
extraction from generic "addressing fields".

1 files changed, 7 insertions, 6 deletions

diff --git a/addons/mail/wizard/mail_compose_message.py b/addons/mail/wizard/mail_compose_message.py
index f4cc59c2a34..bf794fc2a7f 100644
--- a/addons/mail/wizard/mail_compose_message.py
+++ b/addons/mail/wizard/mail_compose_message.py
@@ -281,14 +281,15 @@ class MailComposer(models.TransientModel):
 
         blacklisted_rec_ids = []
         if mass_mail_mode and hasattr(self.env[self.model], "_primary_email"):
-            blacklist = self.env['mail.blacklist'].sudo().search([]).mapped('email')
+            BL_sudo = self.env['mail.blacklist'].sudo()
+            blacklist = set(email.lower() for email in BL_sudo.search([]).mapped('email'))
             if blacklist:
                 [email_field] = self.env[self.model]._primary_email
-                sql = """ SELECT id from %s WHERE LOWER(%s) = any (array[%s]) AND id in (%s)""" % \
-                      (self.env[self.model]._table, email_field, ', '.join("'" + rec + "'" for rec in blacklist),
-                       ', '.join(str(res_id) for res_id in res_ids))
-                self._cr.execute(sql)
-                blacklisted_rec_ids = [rec[0] for rec in self._cr.fetchall()]
+                targets = self.env[self.model].browse(res_ids).read([email_field])
+                blacklisted_rec_ids = [r['id']
+                                        for r in targets
+                                        if r[email_field]
+                                        if r[email_field].lower() in blacklist]
         for res_id in res_ids:
             # static wizard (mail.message) values
             mail_values = {