11 work_item_client = connection.clients.get_work_item_tracking_client()
12
13 # Define the query to retrieve the desired work items
14 wiql = Wiql(query=f"SELECT * FROM WorkItems WHERE [System.TeamProject] = '{project_name}' AND [System.Id] IN (SELECT [Id] FROM WorkItemLinks WHERE [Source].[System.Id] IN ({query_id}))")15
16 # Run the query and get the results
17 query_results = work_item_client.query_by_wiql(wiql).work_itemsConstructing SQL query using user provided data is insecure. It makes application vulnerable to SQL injection attacks. An SQL injection attack consists of the insertion or “injection” of an SQL query via the input data given to an application. It is a very common attack vector. Unless care is taken to sanitize and control the input data when building such SQL statement strings, an injection attack becomes possible. It is possible for an attacker to craft queries to read, modify, or delete sensitive information from the database and sometimes even shut it down or execute arbitrary operating system commands.
It is recommended to ensure that the user-provided data is properly escaped and validated. Modern database adapters come with built-in tools for preventing Python SQL injection by using query parameters.
Since this is an audit issue, some occurrences may be harmless here. The goal is to bring the issue to attention. Please make sure that the input string is trusted. If the occurrences don't seem to be valid, please feel free to ignore them.
Bad practice
cursor = connection.cursor()
cursor.execute("SELECT id FROM userdata WHERE Name =%s;" % name) # Sensitve. Query constructed based on user's input
Recommended
cursor = connection.cursor()
# # Username is passed as a named parameter.
# Database will use the specified type and value of username when executing the query
cursor.execute("SELECT * FROM userdata WHERE Name = %s;", (name,))
References:
- SQL injection
- SQL injection attacks with Python
- OWASP Top 10 2021 Category A03 - Injection
- SANS Top 25
- CWE-20 - Improper input validation
- CWE 89 - Improper Neutralization of Special Elements used in an SQL Command