Scanning embedded SQL calls / FN SQL Injection for Python

SonarQube Cloud
, , ,
1

We have a monorepo that embeds SQL query or PSQL, MSSQL, etc… from Python and Go code.

Once in a while a more Junior engineer will use string formatting to pass values to the SQL query instead of using the driver’s argument passing which is much safer.

Is this something that sonar can detect and alert on, or is the SQL scanning purely for independent SQL files?

3

Hello @sodul,

I suspect you want to detect SQL injection issues if tainted data is used to build an SQL query.
Sonar can do that for Python if you have the SQ Developer Edition+ or if you use SonarCloud.
The same feature doesn’t exist yet for Go, even if it is highly requested by other users.

Would you be able to share a snippet of Python + SQL to be sure we are talking about the same thing?

Thanks
Alex

6

Yes, we have SQL code embedded into our python code.

The sample code would be:

        records = []
        with self.db.connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    'select id, start_timestamp from records'
                    f' where org_id={org_id} and expired > NOW();'
                )
                for record in cursor.fetchall():
                    records.append(record)

Here I would expect Sonar to tell the developer to pass the org_id as a parameter to cursor.execute() as a way to prevent SQL injections.

We use SonarCloud on private repositories.

7

Hello,

I agree with you that if the org_id comes from a tainted input, the rule S3649 should be raised.
Also, you should have the Security Hotspot python:S2077 triggered to inform the developer that this is not a best practice to build SQL queries using concatenation. Can you check that in the Security Hotspots page?

It would be awesome if you could add your snippet of code to a reproducer (one single Python file) so we can investigate why S3649 is not raised. With what you shared, we can’t say because we have no visibility of where the org_id comes from.

Alex

8

I’ll see what I can do with regards to sharing a more comprehensive example. This is code on private repositories which I cannot share on a public forum.