Using Parameterized Queries and Prepared Statements in Python

Course: Python Training

Topic: Using Parameterized Queries and Prepared Statements

Course Link: Python Training Course – JustAcademy

Demo Link: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction to Parameterized Queries

Parameterized queries are a secure way to execute SQL statements from Python applications. Instead of inserting user-provided values directly into an SQL query string, developers use placeholders and pass the values separately to the database driver.

This approach helps prevent SQL injection attacks, handles special characters correctly, improves code readability, and makes database operations more reliable.

Parameterized queries are especially important when an application accepts user input, such as usernames, passwords, search terms, product IDs, email addresses, or payment records.

Example of a Parameterized Query

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

student_name = "Rahul"

cursor.execute(
    "SELECT * FROM students WHERE name = ?",
    (student_name,)
)

results = cursor.fetchall()

for row in results:
    print(row)

connection.close()

In this example, the question mark is a placeholder. The value of student_name is supplied separately as a parameter rather than being concatenated into the SQL statement.

2. Learning Objectives

• Understand parameterized queries and prepared statements.
• Identify the security risks of constructing SQL with string concatenation.
• Use placeholders with Python database libraries.
• Perform secure SELECT, INSERT, UPDATE, and DELETE operations.
• Understand positional and named parameters.
• Handle query results, exceptions, commits, and rollbacks.
• Apply secure database practices in real-world Python applications.

3. What Are Prepared Statements?

A prepared statement is an SQL statement that is prepared by a database system or driver for execution with supplied parameters. The statement's SQL structure is kept separate from the data values.

Depending on the database engine and driver, preparation may happen explicitly or internally when a parameterized query is executed. Therefore, parameterized queries and prepared statements are closely related, but they are not identical in every implementation.

How Prepared Statements Work

1. Write the SQL statement: Define the query using placeholders.
2. Provide parameter values: Supply data separately from the SQL command.
3. Execute the statement: The database driver binds the supplied values to the placeholders.
4. Process the result: Fetch records or inspect the number of affected rows.

For example, the query SELECT * FROM students WHERE age > ? defines the SQL operation, while the supplied age value determines which records match.

4. Why Parameterized Queries Are Important

5. Understanding SQL Injection

SQL injection occurs when untrusted input is incorporated into an SQL statement in a way that changes the intended query structure. An attacker may manipulate a vulnerable query to retrieve unauthorized data or alter database records.

Unsafe Example: String Concatenation

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

username = input("Enter username: ")

# Unsafe: user input is inserted into SQL text
query = (
    "SELECT * FROM users WHERE username = '"
    + username
    + "'"
)

cursor.execute(query)

print(cursor.fetchall())
connection.close()

Why is this unsafe? The application treats user input as part of the SQL command. Special characters or malicious SQL fragments may alter the query's intended meaning.

Secure Example: Parameterized Query

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

username = input("Enter username: ")

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

print(cursor.fetchall())
connection.close()

Here, the username is bound as a value. Even if it contains characters that resemble SQL syntax, the parameter is not interpreted as part of the query structure.

Important: Parameterization protects values, but it does not replace authentication, authorization, input validation, least-privilege database permissions, or safe error handling.

6. Using Parameterized Queries with SQLite

Python includes the sqlite3 module, which supports parameterized queries without requiring an external package. SQLite uses the question mark (?) for positional placeholders and also supports named placeholders.

Creating a Sample Database Table

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL,
        email TEXT UNIQUE
    )
""")

connection.commit()
connection.close()

This example creates a students table with an automatically generated ID, a required name, a required age, and a unique email address.

Inserting Data Securely

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

student = ("Amit Sharma", 21, "[email protected]")

cursor.execute(
    "INSERT INTO students (name, age, email) VALUES (?, ?, ?)",
    student
)

connection.commit()

print("Student inserted successfully.")

connection.close()

The three placeholders correspond to the name, age, and email values. The parameter tuple must contain the values in the same order as the placeholders.

Retrieving Data Securely

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

minimum_age = 18

cursor.execute(
    "SELECT id, name, age, email FROM students WHERE age >= ?",
    (minimum_age,)
)

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

The comma in (minimum_age,) is important. It creates a single-element tuple in Python. Writing (minimum_age) alone does not create a tuple.

Updating Data Securely

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

new_age = 22
student_id = 1

cursor.execute(
    "UPDATE students SET age = ? WHERE id = ?",
    (new_age, student_id)
)

connection.commit()

print("Rows updated:", cursor.rowcount)

connection.close()

Both the new age and the student ID are passed as parameters. The WHERE clause limits the update to the intended record.

Deleting Data Securely

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

student_id = 1

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (student_id,)
)

connection.commit()

print("Rows deleted:", cursor.rowcount)

connection.close()

Always review the WHERE clause before executing DELETE or UPDATE statements. Omitting a restrictive condition may affect every row in the table.

7. Positional Parameters and Named Parameters

Database drivers may support different placeholder styles. SQLite supports both positional and named parameters. Choose the syntax documented by the database driver you are using.

Positional Parameters

cursor.execute(
    "SELECT * FROM students WHERE name = ? AND age = ?",
    ("Amit Sharma", 21)
)

Positional parameters use placeholders such as ?. Values must be supplied in the correct order.

Named Parameters

cursor.execute(
    """
    SELECT * FROM students
    WHERE name = :student_name AND age = :student_age
    """,
    {
        "student_name": "Amit Sharma",
        "student_age": 21
    }
)

Named parameters associate values with meaningful placeholder names. They can make queries easier to read when many parameters are involved.

Comparison of Parameter Styles

8. Using Parameters with MySQL

When working with MySQL, use the placeholder syntax supported by your selected Python connector. For example, the commonly used mysql-connector-python package uses %s placeholders for values, rather than SQLite's question marks.

Installing the MySQL Connector

pip install mysql-connector-python

Executing a Parameterized MySQL Query

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="college"
)

cursor = connection.cursor()

student_id = 2

cursor.execute(
    "SELECT id, name, age FROM students WHERE id = %s",
    (student_id,)
)

for row in cursor.fetchall():
    print(row)

cursor.close()
connection.close()

Replace the connection settings with your own database configuration. In production applications, avoid hardcoding database passwords in source code; use environment variables or a suitable secrets-management system.

Remember: Placeholder styles differ across drivers. Always check the connector documentation instead of copying placeholder syntax from a different database library.

9. Using Parameters with PostgreSQL

The popular Psycopg driver for PostgreSQL uses %s placeholders for values in its standard parameterized execution style. The SQL query and parameter values should be supplied separately.

import psycopg

connection = psycopg.connect(
    "dbname=college user=your_username password=your_password"
)

try:
    with connection.cursor() as cursor:
        student_id = 2

        cursor.execute(
            "SELECT id, name, age FROM students WHERE id = %s",
            (student_id,)
        )

        for row in cursor.fetchall():
            print(row)

    connection.commit()
finally:
    connection.close()

Install the appropriate Psycopg package for your environment before running this example. Connection settings and driver versions may require adjustments.

10. Parameterizing Multiple Records

When inserting several records, use executemany() where the driver supports it. This method applies one parameterized SQL statement to multiple sets of values.

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

students = [
    ("Neha", 20, "[email protected]"),
    ("Karan", 22, "[email protected]"),
    ("Priya", 19, "[email protected]")
]

try:
    cursor.executemany(
        "INSERT INTO students (name, age, email) VALUES (?, ?, ?)",
        students
    )
    connection.commit()
    print("Student records inserted successfully.")
except sqlite3.Error:
    connection.rollback()
    raise
finally:
    connection.close()

The database driver processes each row using the same SQL structure. If an error occurs, the rollback prevents the current transaction from being committed partially when the transaction and database configuration support that behavior.

11. Handling Errors and Transactions

Parameterized queries do not eliminate database errors. Duplicate values, missing tables, invalid data, connection problems, and constraint violations may still occur.

import sqlite3

connection = sqlite3.connect("college.db")

try:
    cursor = connection.cursor()

    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES (?, ?, ?)",
        ("Riya", 20, "[email protected]")
    )

    connection.commit()
    print("Transaction completed successfully.")

except sqlite3.IntegrityError:
    connection.rollback()
    print("A database constraint was violated.")

except sqlite3.Error:
    connection.rollback()
    print("A database operation failed.")

finally:
    connection.close()

This example catches integrity errors separately from other SQLite errors. In production, log appropriate technical details securely, but avoid exposing database internals or sensitive information to end users.

12. Parameterized Queries for Search and Filtering

Search features frequently accept user input. Parameterization allows the application to safely search using values supplied by users.

import sqlite3

connection = sqlite3.connect("college.db")
cursor = connection.cursor()

search_name = input("Enter a student name: ")

cursor.execute(
    "SELECT id, name, age FROM students WHERE name = ?",
    (search_name,)
)

for row in cursor.fetchall():
    print(row)

connection.close()

Searching with LIKE

search_term = "Amit"

cursor.execute(
    "SELECT id, name FROM students WHERE name LIKE ?",
    (f"%{search_term}%",)
)

results = cursor.fetchall()

The wildcard characters are part of the parameter value, not concatenated into the SQL statement. Note that user-provided % and _ characters retain their LIKE wildcard meanings unless they are escaped intentionally.

13. Can SQL Identifiers Be Parameterized?

Value placeholders generally cannot be used for table names, column names, SQL keywords, or sort directions. For example, attempting to bind a column name as a normal value will not dynamically select that column.

Unsafe Dynamic SQL

# Do not directly insert untrusted input into SQL identifiers
query = "SELECT * FROM students ORDER BY " + user_column

Safer Approach Using an Allowlist

allowed_columns = {"name", "age", "id"}

user_column = input("Sort by (name, age, id): ")

if user_column not in allowed_columns:
    raise ValueError("Invalid sort column")

query = f"SELECT id, name, age FROM students ORDER BY {user_column}"

cursor.execute(query)

This example allows only predefined column names. Because the interpolated identifier is selected from a fixed allowlist, arbitrary user input cannot introduce a new SQL expression through this variable. If the database or driver provides identifier-quoting utilities, use those where appropriate as an additional safeguard.

14. Common Mistakes to Avoid

15. Best Practices for Using Parameterized Queries

• Always parameterize user-supplied values: Use the placeholder syntax supported by your database driver.
• Keep SQL and data separate: Do not build query strings by concatenating untrusted input.
• Validate business rules: Check required fields, acceptable ranges, and expected data formats before performing database operations.
• Use transactions: Group related changes and commit or roll back them as appropriate.
• Use least-privilege database accounts: Give the application only the permissions it requires.
• Close database resources: Use context managers or appropriate cleanup logic to manage cursors and connections.
• Protect credentials: Store passwords and connection secrets outside application source code.
• Test security scenarios: Verify that unusual characters and SQL-like input are treated as data.
• Use allowlists for dynamic identifiers: Validate table names, column names, and sort directions before constructing SQL.
• Measure performance: Use database indexes and appropriate query design rather than assuming parameterization alone makes a query faster.

16. Practical Example: Student Management Application

The following program demonstrates creating a database table, inserting a student, retrieving the student record, and handling database errors using parameterized queries.

import sqlite3

def create_table(connection):
    connection.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            age INTEGER NOT NULL,
            email TEXT UNIQUE
        )
    """)

def add_student(connection, name, age, email):
    connection.execute(
        """
        INSERT INTO students (name, age, email)
        VALUES (?, ?, ?)
        """,
        (name, age, email)
    )
    connection.commit()

def find_student(connection, student_id):
    cursor = connection.execute(
        """
        SELECT id, name, age, email
        FROM students
        WHERE id = ?
        """,
        (student_id,)
    )
    return cursor.fetchone()

def main():
    connection = sqlite3.connect("college.db")

    try:
        create_table(connection)

        add_student(
            connection,
            "Ananya Sharma",
            21,
            "[email protected]"
        )

        student = find_student(connection, 1)

        if student:
            print("Student found:", student)
        else:
            print("No student found.")

    except sqlite3.IntegrityError:
        connection.rollback()
        print("The record violates a database constraint.")

    except sqlite3.Error:
        connection.rollback()
        print("A database operation failed.")

    finally:
        connection.close()

if __name__ == "__main__":
    main()

Note: This demonstration assumes the inserted record has ID 1, which may not be true if the database already contains records. In a production application, retrieve the inserted ID using the driver's supported mechanism or query by a unique identifier.

Expected Output

Student found: (1, 'Ananya Sharma', 21, '[email protected]')

The exact output depends on the database's existing contents. If the email already exists, the unique constraint will reject the insert.

17. Performance Considerations

Parameterized queries can support efficient database execution, but performance depends on the database engine, driver, query structure, indexing, and workload.

• Use executemany() for suitable bulk operations.
• Create indexes on frequently searched or joined columns when appropriate.
• Select only the columns the application needs instead of always using SELECT *.
• Use query execution plans to identify expensive operations.
• Reuse database connections appropriately in applications that handle many requests.
• Use prepared-statement reuse or server-side preparation when supported and beneficial for the workload.

Do not assume that every call to a parameterized query automatically reuses a prepared statement. The behavior depends on the database and connector implementation.

18. Testing Parameterized Queries

Testing helps confirm that queries return the expected results, handle invalid data correctly, and do not treat user input as executable SQL.

Testing Checklist

• Test with ordinary text, numbers, and empty values.
• Test text containing apostrophes, such as O'Brien.
• Test input containing SQL-like characters, such as ' OR 1=1 --, and confirm that it is treated as a value.
• Test duplicate records and database constraint violations.
• Test queries with missing or incorrectly supplied parameters.
• Verify that UPDATE and DELETE statements affect only the intended records.
• Confirm that failed transactions are handled correctly.

19. Hands-On Exercises

Exercise 1: Insert Employee Records

Create an employees table with employee ID, name, department, and salary. Write a parameterized INSERT query to add at least five employees.

Exercise 2: Search for Products

Create a products table and write a parameterized SELECT query that retrieves products below a user-provided price.

Exercise 3: Update Customer Information

Write a parameterized UPDATE query that changes a customer's email address using the customer ID.

Exercise 4: Delete a Record Safely

Build a program that deletes an employee by ID after validating the requested operation.

Exercise 5: Compare Unsafe and Safe Queries

Write one intentionally vulnerable example in a local practice environment, then replace it with a parameterized query. Explain why the secure version preserves the SQL statement's intended structure.

Exercise 6: Named Parameters

Use SQLite named placeholders to find students by name and age. Compare the readability of named and positional parameters.

Exercise 7: Database Transaction

Insert two related records in a single transaction. If either operation fails, roll back the transaction and verify that the changes were not partially committed.

20. Interview Questions and Answers

Q1. What is a parameterized query?

Answer: A parameterized query is an SQL statement that uses placeholders for data values. The application supplies those values separately through the database driver.

Q2. What is the main advantage of parameterized queries?

Answer: Their primary security benefit is preventing user-supplied values from being interpreted as SQL syntax, helping protect against SQL injection.

Q3. What is SQL injection?

Answer: SQL injection is a security vulnerability in which untrusted input changes the intended structure or meaning of an SQL statement.

Q4. What is the difference between a parameterized query and a prepared statement?

Answer: A parameterized query separates SQL structure from its values. A prepared statement is a statement prepared by the database or driver for execution with parameters. Parameterized execution may use preparation internally, depending on the implementation.

Q5. Which placeholder does SQLite use for positional parameters?

Answer: SQLite supports the question mark placeholder, such as ?, and also supports named placeholders such as :name.

Q6. Why is (value,) used for a single parameter in Python?

Answer: The trailing comma creates a one-element tuple. Parentheses alone do not make a tuple.

Q7. Can placeholders be used for table names?

Answer: Generally, no. Value placeholders are intended for data values. Dynamic identifiers should be selected from an allowlist or composed using a safe identifier-handling feature provided by the database driver.

Q8. Can parameterized queries prevent every database security problem?

Answer: No. They help prevent SQL injection through parameterized values but do not replace access controls, authorization, validation, secure credential management, or safe handling of dynamic SQL identifiers.

Q9. What is the purpose of executemany()?

Answer: It executes a parameterized statement against multiple sets of parameters. It is useful for suitable bulk insert and update operations.

Q10. Why is commit() needed?

Answer: It commits a transaction so that its changes are saved according to the database's transaction semantics. The exact behavior depends on the database and connection configuration.

Q11. How do parameterized queries handle apostrophes in text?

Answer: The database driver binds the text as a value, so an apostrophe is handled as data rather than as a delimiter in the SQL statement.

Q12. Do parameterized queries always improve performance?

Answer: No. They may offer performance benefits in some situations, especially when prepared statements can be reused, but actual performance depends on the driver, database, and query workload.

21. Quick Revision Checklist

• Parameterized queries separate SQL code from data values.
• Never concatenate untrusted values directly into SQL statements.
• Use the placeholder syntax supported by your database driver.
• Supply parameters in the correct order or with the correct names.
• Use transactions and appropriate rollback handling for related database operations.
• Use allowlists or safe identifier composition for dynamic SQL identifiers.
• Protect credentials and limit database permissions.
• Test normal inputs, unusual characters, constraint violations, and error scenarios.

22. Conclusion

Parameterized queries and prepared statements are essential techniques for building secure and maintainable Python database applications. They keep data separate from SQL instructions, reduce SQL injection risks, simplify query construction, and support reliable database operations.

By practicing with SQLite, MySQL, and PostgreSQL, developers can learn how different database drivers handle parameters, transactions, and statement execution. Combining parameterized queries with proper validation, authorization, error handling, and database permissions creates a stronger foundation for real-world applications.

Explore the Python Training Course at JustAcademy to continue learning Python programming and database development.

To learn more about the course, register for a course demo or visit the JustAcademy website.

Additional Learning Resources

• Python Official Documentation – sqlite3
• MySQL Connector/Python Documentation
• Psycopg Documentation for PostgreSQL
• OWASP – SQL Injection