# SQL Injection
**SQL injection** is a type of *injection attack*. Injection attacks occur when maliciously crafted inputs are submitted by an attacker, causing an application to perform an unintended action. In a SQL injection attack, untrusted input from the HTTP request or frontend is inserted into a SQL statement insecurely, allowing an attacker to run arbitrary commands on the database. This allows an attacker to steal and manipulate sensitive data, or inject other malicious code that can be used to escalate their attack.
In most applications, SQL statements are defined in the codebase or configuration, then executed according to a set of parameters passed in from an external source. You should use *parameterized statements* to ensure these parameters are *bound* to the SQL statement securely, so attackers cannot inject extra SQL statements or change the logic of an existing statement.
## Parameterized Statements in Python
Programming languages talk to SQL databases using *database drivers.* A driver allows an application to construct and run SQL statements against a database, extracting and manipulating data as needed. *Parameterized statements* make sure that the parameters (i.e. inputs) passed into SQL statements are treated safely.
For example, a secure way of running a SQL query in MySQL using a parameterized statement would be:
“`python import pymysql.cursorsconnection = pymysql.connect(**MYSQL_CONNECTION_DETAILS)with connection: with connection.cursor() as cursor: # Construct the SQL statement we want to run, specifying the parameter. # Execute the SQL passing in a parameter to bind. for result in cursor.fetchone(): |
Contrast this to explicit interpolation of the SQL string, which is **very, very dangerous**:
“`python import pymysql.cursorsconnection = pymysql.connect(**MYSQL_CONNECTION_DETAILS)with connection: with connection.cursor() as cursor: # Bad, bad news! Don’t construct the query with string concatenation. # When this query gets run… for result in cursor.fetchone(): |
The key difference is the data being passed to the `execute(…)` method. In the first case the parameterized string and the parameters are passed to the database separately, which allows the driver to correctly interpret them. In the second case, the full SQL statement is constructed before the driver is invoked, meaning we are vulnerable to maliciously crafted parameters. For example, if the attacker passes the `email` parameter value as `’; drop table users–`, they will be able to delete the `users` table altogether.
Below are examples of how use parameterized statements in the major database drivers. Since most database drivers implement the Python Database API Specification (PEP-249), they look very similar.
### MySQL
“`python import pymysql.cursorsconnection = pymysql.connect(**MYSQL_CONNECTION_DETAILS)with connection: with connection.cursor() as cursor: cursor.execute(‘SELECT * FROM users WHERE email = %(email)s’, dict(email=email)) for result in cursor.fetchone(): |
### PostgreSQL
“`python import psycopg2connection = psycopg2.connect(**POSTGRESQL_CONNECTION_DETAILS)with connection: with connection.cursor() as cursor: cursor.execute(‘SELECT * FROM users WHERE email = %(email)s’, dict(email=email)) for result in cursor.fetchone(): |
### SQLite
“`python import sqlite3connection = sqlite3.connect(SQLITE_DATABASE_NAME)with connection: with connection.cursor() as cursor: cursor.execute(‘SELECT * FROM users WHERE email = %(email)s’, dict(email=email)) for result in cursor.fetchone(): |
### SQL Server
“`python import pymssqlconnection = pymssql.connect(**MS_SQL_CONNECTION_DETAILS)with connection: with connection.cursor() as cursor: cursor.execute(‘SELECT * FROM users WHERE email = %(email)s’, dict(email=email)) for result in cursor.fetchone(): |
### Oracle
“`python import cx_Oracle as oracledbconnection = oracledb.connect(ORACLE_DB_CONNECTION_STRING)with connection: with connection.cursor() as cursor: cursor.execute(‘SELECT * FROM users WHERE email = %(email)s’, dict(email=email)) for result in cursor.fetchone(): |
## Other Considerations.
* Use of an *Object-Relational Mapping* library like SQLAlchemy will reduce the amount of SQL statements you have to write in your code, and will push you towards best practice when interacting with the database.
* Connecting to the database under an account with limited permissions – following the *principle of least privilege* – is always a good idea, since it mitigates the harm an attacker can do.
## CWEs
* [CWE-89](https://cwe.mitre.org/data/definitions/89.html)