python:S2077 / pythonsecurity:S3649 Odoo SQL queries

Rules and Languages Report False-positive / False-negative...
1

Hey,

Odoo SQL queries are not really detected via SonarQube. We found out that some of our developers created code which is vulnerable to SQL injections but it wasn’t detected.

Example Code:

    def get_used_purchase_orders(self):
        self.ensure_one()
        self.env.cr.execute("""
            SELECT COALESCE(SUM(amount_total), 0), COUNT(*)
            FROM purchase_order
            WHERE service_id = %s
            AND state IN ('purchase', 'done')
            AND (company_id = %s OR company_id IS NULL)
        """ % (self.id, self.env.user.company_id.id))
        return self.env.cr.fetchall()[0]

Odoo uses psycopg2 under the hood. So the same rules for psycopg2 should be applied here. The correct code would be:

    def get_used_purchase_orders(self):
        self.ensure_one()
        self.env.cr.execute("""
            SELECT COALESCE(SUM(amount_total), 0), COUNT(*)
            FROM purchase_order
            WHERE service_id = %s
            AND state IN ('purchase', 'done')
            AND (company_id = %s OR company_id IS NULL)
        """, (self.id, self.env.user.company_id.id))
        return self.env.cr.fetchall()[0]

Here you find the code of the execute function: odoo/odoo/sql_db.py at bffaf28377f0adee0c988a98d6b730e7fbe30a6c · odoo/odoo · GitHub

Developer documentation: ORM API — Odoo 18.0 documentation

As reference: The tool bandit marks this as an issue:

>> Issue: [B608:hardcoded_sql_expressions] Possible SQL injection vector through string-based query construction.
   Severity: Medium   Confidence: Medium
   CWE: CWE-89 (https://cwe.mitre.org/data/definitions/89.html)
   More Info: https://bandit.readthedocs.io/en/1.8.6/plugins/b608_hardcoded_sql_expressions.html
   Location: addons/blubb/models/purchase_order.py:317:28
316	        self.ensure_one()
317	        self.env.cr.execute("""
318	                        SELECT COALESCE(SUM(amount_total), 0), COUNT(*)
319                         FROM purchase_order
320	                        WHERE service_id = %s
321	                        AND state IN ('purchase', 'done')
322	                        AND (company_id = %s OR company_id IS NULL)
323	                    """ % (self.id, self.env.user.company_id.id))
324	        return self.env.cr.fetchall()[0]
  • What language is this for?
    • Python
  • Which rule?
    • python:S2077 or pythonsecurity:S3649
  • Why do you believe it’s a false-positive/false-negative?
    • It’s a false-negative because the code uses normal python formatting of the query and is not passing the arguments separately to the execute function.
  • We are using
    • SonarQube Server / Community Build - which version?
      • Enterprise Edition v2025.3.1 (109879)
      • SonarScanner CLI 7.2.0.5079
  • How can we reproduce the problem? Give us a self-contained snippet of code (formatted text, no screenshots)
    • see above
1 Like
3

Hi @SyneX,

Welcome to the forums! Thanks for your report, I am adding it to my current batch of investigations, this should not happen. I’ll come back to you once I am done, with an explanation.

Cheers!

Loris

5

Hi @SyneX,

With the current information you provided, I am not sure we would detect it, and I do not know Odoo development so I feel like the rudimentary knowledge I built during investigation might not be enough for now.

I need to understand how the vulnerability materialized, can you confirm my understanding or tell me if I am wrong?

  1. The class which contains the functions you sent is a class which extends odoo.models.<AnyModel>
  2. This class contains fields, and for example your user field contains nested fields, such as company_id ?
  3. You place code is placed in the addons modules of your odoo server, and then installed
  4. Then, from the Odoo UI, you click on the new feature of this model and face a form with the fields you defined. Is that right?
  5. Then, you can control user.company_id.id from this place and it will be sent to the piece of code you sent.

Did I sum it up correctly? And therefore, when we analyze your code, we need to assume that all fields that have been defined at the root of your class are attack vectors and are accessible via self.env. Is that right?

Hence why self.env.user.company_id.id is the attack vector in your example?

Cheers,

Loris

6

Hi @SyneX, gentle ping to ask you if you could have a look at my previous message. I think having your point of view as an actual Odoo user could help us a lot :folded_hands: