Codemod: sql-injection Semgrep

[clavedeluna]

https://semgrep.dev/r?q=python.django.security.injection.sql.sql-injection-using-db-cursor-execute.sql-injection-db-cursor-execute

shows up if you run semgrep tests/samples/

This rule also shows up, do we want to combine it? python.lang.security.audit.formatted-sql-query.formatted-sql-query

[clavedeluna]

@drdavella if you run semgrep tests/samples, you'll notice this

  tests/samples/fix_sonar_sql_parameterization.py
    ❯❱ python.django.security.injection.sql.sql-injection-using-db-cursor-execute.sql-injection-db-cursor-
       execute
          User-controlled data from a request is passed to 'execute()'. This could lead to a SQL
          injection and therefore protected information could be leaked. Instead, use django's
          QuerySets, which are built with query parameterization and therefore not vulnerable to
          sql injection. For example, you could use `Entry.objects.filter(date=2006)`.
          Details: https://sg.run/qx7y

           10┆ user = request.args["user"]
           11┆ sql = """SELECT user FROM users WHERE user = \'%s\'"""
           12┆
           13┆ conn = sqlite3.connect("example")
           14┆ conn.cursor().execute(sql % (user))

    ❯❱ python.lang.security.audit.formatted-sql-query.formatted-sql-query
          Detected possible formatted SQL query. Use parameterized queries instead.
          Details: https://sg.run/EkWw

           14┆ conn.cursor().execute(sql % (user))

   ❯❯❱ python.sqlalchemy.security.sqlalchemy-execute-raw-query.sqlalchemy-execute-raw-query
          Avoiding SQL string concatenation: untrusted input concatenated with raw SQL query can
          result in SQL Injection. In order to execute raw query safely, prepared statement should
          be used. SQLAlchemy provides TextualSQL to easily used prepared statement with named
          parameters. For complex SQL composition, use SQL Expression Language or Schema Definition
          Language. In most cases, SQLAlchemy ORM will be a better option.
          Details: https://sg.run/2b1L

           14┆ conn.cursor().execute(sql % (user))

   ❯❯❱ python.django.security.injection.tainted-sql-string.tainted-sql-string
          Detected user input used to manually construct a SQL string. This is usually bad practice
          because manual construction could accidentally result in a SQL injection. An attacker
          could use a SQL injection to steal or modify contents of the database. Instead, use a
          parameterized query which is available by default in most database engines.
          Alternatively, consider using the Django object-relational mappers (ORM) instead of raw
          SQL queries.
          Details: https://sg.run/PbZp

           14┆ conn.cursor().execute(sql % (user))

   ❯❯❱ python.flask.security.injection.tainted-sql-string.tainted-sql-string
          Detected user input used to manually construct a SQL string. This is usually bad practice
          because manual construction could accidentally result in a SQL injection. An attacker
          could use a SQL injection to steal or modify contents of the database. Instead, use a
          parameterized query which is available by default in most database engines.
          Alternatively, consider using an object-relational mapper (ORM) such as SQLAlchemy which
          will protect your queries.
          Details: https://sg.run/JxZj

           14┆ conn.cursor().execute(sql % (user))

line 14 is triggering a total of 5 semgrep rules. How do we want to handle this? one codemod for each? one codemod that contains all rules?

[drdavella]

@clavedeluna yes I think these can all be handled by the same codemod. We should make sure that when the codemod fixes one of the findings, all of them are marked as fixed.