CodeQL documentation
CodeQL resources

SQL query built from user-controlled sources

ID: py/sql-injection Kind: path-problem Security severity: 8.8 Severity: error Precision: high Tags: - security - external/cwe/cwe-089 Query suites: - python-code-scanning.qls - python-security-extended.qls - python-security-and-quality.qls

Click to see the query in the CodeQL repository

If a database query (such as a SQL or NoSQL query) is built from user-provided data without sufficient sanitization, a user may be able to run malicious database queries.

This also includes using the TextClause class in the [SQLAlchemy](https://pypi.org/project/SQLAlchemy/) PyPI package, which is used to represent a literal SQL fragment and is inserted directly into the final SQL when used in a query built using the ORM.

Recommendation

Most database connector libraries offer a way of safely embedding untrusted data into a query by means of query parameters or prepared statements.

Example

In the following snippet, a user is fetched from the database using three different queries.

In the first case, the query string is built by directly using string formatting from a user-supplied request parameter. The parameter may include quote characters, so this code is vulnerable to a SQL injection attack.

In the second case, the user-supplied request attribute is passed to the database using query parameters. The database connector library will take care of escaping and inserting quotes as needed.

In the third case, the placeholder in the SQL string has been manually quoted. Since most databaseconnector libraries will insert their own quotes, doing so yourself will make the code vulnerable to SQL injection attacks. In this example, if username was ;DROPALLTABLES--, the final SQL query would be SELECT*FROMusersWHEREusername='';DROPALLTABLES--''

fromdjango.conf.urlsimporturlfromdjango.dbimportconnectiondefshow_user(request,username):withconnection.cursor()ascursor:# BAD -- Using string formattingcursor.execute("SELECT * FROM users WHERE username = '%s'"%username)user=cursor.fetchone()# GOOD -- Using parameterscursor.execute("SELECT * FROM users WHERE username = %s",username)user=cursor.fetchone()# BAD -- Manually quoting placeholder (%s)cursor.execute("SELECT * FROM users WHERE username = '%s'",username)user=cursor.fetchone()urlpatterns=[url(r'^users/(?P<username>[^/]+)$',show_user)]

References

close