164 db_handler.connection.ping(reconnect=True, attempts=3)
165 statement = {
166 "sqlite": f"SELECT user_id, level, xp, total_xp, points FROM users ORDER BY {order_by} DESC LIMIT ?",
167 "mysql": f"SELECT user_id, level, xp, total_xp, points FROM users ORDER BY {order_by} DESC LIMIT %s",168 }
169 db_handler.execute(
170 statement,163 if db_handler.db_type == "mysql":
164 db_handler.connection.ping(reconnect=True, attempts=3)
165 statement = {
166 "sqlite": f"SELECT user_id, level, xp, total_xp, points FROM users ORDER BY {order_by} DESC LIMIT ?",167 "mysql": f"SELECT user_id, level, xp, total_xp, points FROM users ORDER BY {order_by} DESC LIMIT %s",
168 }
169 db_handler.execute(127 )
128 statement = {
129 "sqlite": f"DELETE FROM permissions WHERE command_id NOT IN ({placeholders})",
130 "mysql": f"DELETE FROM permissions WHERE command_id NOT IN ({placeholders})",131 }
132 self.db.execute(statement, tuple(self.command_id_list))
133 self.command_id_list.clear()126 "?" if self.db.db_type == "sqlite" else "%s" for _ in self.command_id_list
127 )
128 statement = {
129 "sqlite": f"DELETE FROM permissions WHERE command_id NOT IN ({placeholders})",130 "mysql": f"DELETE FROM permissions WHERE command_id NOT IN ({placeholders})",
131 }
132 self.db.execute(statement, tuple(self.command_id_list))125 await append_guild(guild_id)
126 statement = {
127 "sqlite": f"UPDATE guild SET {key} = ? WHERE guild_id = ?",
128 "mysql": f"UPDATE guild SET {key} = %s WHERE guild_id = %s",129 }
130 db_handler.execute(statement, (value, str(guild_id)))
131 print(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