# 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.
sql = “SELECT * FROM users WHERE email = %(email)s”

# Execute the SQL passing in a parameter to bind.
cursor.execute(sql, dict(email=email))

for result in cursor.fetchone():
# Do something with the data returned.
pass
“`

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.
sql = “SELECT * FROM users WHERE email = ‘” + email + “‘”

# When this query gets run…
cursor.execute(sql)

for result in cursor.fetchone():
# …an attacker may have compromised your database.
pass
“`

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():
pass
“`

### 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():
pass
“`

### 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():
pass
“`

### 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():
pass
“`

### 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():
pass
“`

## 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)

About ShiftLeft

ShiftLeft empowers developers and AppSec teams to dramatically reduce risk by quickly finding and fixing the vulnerabilities most likely to reach their applications and ignoring reported vulnerabilities that pose little risk. Industry-leading accuracy allows developers to focus on security fixes that matter and improve code velocity while enabling AppSec engineers to shift security left.

A unified code security platform, ShiftLeft CORE scans for attack context across custom code, APIs, OSS, containers, internal microservices, and first-party business logic by combining results of the company’s and Intelligent Software Composition Analysis (SCA). Using its unique graph database that combines code attributes and analyzes actual attack paths based on real application architecture, ShiftLeft then provides detailed guidance on risk remediation within existing development workflows and tooling. Teams that use ShiftLeft ship more secure code, faster. Backed by SYN Ventures, Bain Capital Ventures, Blackstone, Mayfield, Thomvest Ventures, and SineWave Ventures, ShiftLeft is based in Santa Clara, California. For information, visit: www.shiftleft.io.

Share

See for yourself – run a scan on your code right now