Python SQL query string formatting

python

sql-injection

string-interpolation

sql-syntax

byAnton Shumikhin·Jan 14, 2025

To avoid falling victim to SQL injection, utilize parameterized queries in Python. This involves using placeholders, such as ? for SQLite and %s for MySQL. Here's the boilerplate to get you started:

import sqlite3

conn = sqlite3.connect('database.db')  # Replace with your actual DB
cursor = conn.cursor()
placeholder_data = ('johndoe',)  # Don't forget the comma for single-value tuples!

cursor.execute("SELECT * FROM users WHERE username = ?", placeholder_data)
results = cursor.fetchall()

print(results)  # Congrats! You just executed a safe query!

Ensure your DB path is correct, and adjust table/column names as per your schema. Even for single value queries, parameters must be passed as a tuple so the library to handles it correctly.

Avoid SQL injection with f-strings

Use f-strings for easy string interpolation while keeping the SQL queries safe:

flavor = 'chocolate'  # Your favorite flavor, perhaps?
query = f"SELECT * FROM cakes WHERE flavor = {flavor};"

Remember - this tactic is useful only when the interpolated strings aren't user input.

SQL string construction using Python's string literal concatenation

Python's string literal concatenation provides a clean way to construct SQL statements, avoiding the need for backslash continuations and enhancing readability:

query = (
    "SELECT id, flavor "  # No need for '+' or '\\'
    "FROM cakes "
    "WHERE sweetness > 5 "  # Too sweet? Never!
    "AND baked_date > '2021-01-01'"  # Only fresh cakes, please.
)

Enhancing readability with `sqlparse`

The sqlparse library in Python helps to reindent and adjust keyword casing for SQL strings, essentially giving them a readability boost:

import sqlparse

formatted_sql = sqlparse.format(query, reindent=True, keyword_case='upper')
print(formatted_sql)  # Pretty AND efficient. What a combo!

Using `inspect.cleandoc` for whitespace control

Python's inspect.cleandoc can be leveraged to remove unnecessary whitespace, especially useful for multi-line SQL strings:

from inspect import cleandoc

query = cleandoc("""
    SELECT
        id, flavor
    FROM
        cakes
    WHERE
        sweetness > 5 AND
        baked_date > '2021-01-01'
""")
print(query)  # Look ma! No unnecessary spaces!

With this, you get a neatly formatted SQL query string, which is easier to read and debug.

Organized SQL syntax with a style guide

Following a 'sql style guide' can hugely enhance the readability of your SQL queries. For consistent formatting and structured queries, a popular SQL style guide is available at https://github.com/meadmaker/sql-style-guide.

Secure dynamic SQL execution

Executing dynamic SQL securely can be achieved by avoiding raw string manipulation and leveraging SQLAlchemy's text feature:

from sqlalchemy.sql import text

stmt = text("SELECT * FROM cakes WHERE flavor = :flavor")  # Create SQL statement
stmt = stmt.bindparams(flavor=flavor)  # Bind parameters securely
result = engine.execute(stmt)  # Safe execution, good job!

Mastering SQL in Python: the cheat sheet

Parameterize: Prevent SQL injection using parameterized queries.
Concatenate: Construct queries elegantly using Python's string literal concatenation.
Format: Use f-strings carefully, best for non-user-controlled data.
Clean: Utilize inspect.cleandoc to remove extra whitespace.
Indent: Make your SQL pretty and legible with sqlparse.
Style: Use a SQL style guide for consistently clear and clean SQL.
Execute: Perform dynamic SQL securely using SQLAlchemy's text.