136 self._conn.commit()
137
138 def get_collection(self, module: str) -> dict:
139 sql = f"SELECT * FROM '{module}'"140 cur = self._execute(module, sql)
141
142 collection = {}131 return True
132
133 def remove(self, module: str, variable: str):
134 sql = f"DELETE FROM '{module}' WHERE var=:var"135 self._execute(module, sql, {"var": variable})
136 self._conn.commit()
137 97 self._lock.release()
98
99 def get(self, module: str, variable: str, default=None):
100 sql = f"SELECT * FROM '{module}' WHERE var=:var"101 cur = self._execute(module, sql, {"tabl": module, "var": variable})
102
103 row = cur.fetchone()Constructing 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