How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access

sql

sql-injection

prepared-statements

exception-handling

byAlex Kataev·Dec 22, 2024

To track the SQL query sent to MS-Access with pyodbc, simulate a query by replacing the parameter placeholders (?) with actual values, just for debugging purposes. Here's how to do it safely:

def mock_query(sql, params):
    print("Mock SQL Query:", sql % tuple(map(repr, params)))

# Example:
sql = "SELECT * FROM Table WHERE Column = ?"
params = (123,)
mock_query(sql, params)

This prints the SQL query executed against the database, including the parameters. This is for debugging only - it's a slippery slope to SQL injection land, so use with caution!

Securing your debug process

In a perfect world, pyodbc would provide us with the SQL strings post-execution, unfortunately, we're not so lucky. This is mainly due to the ODBC driver's behavior and not pyodbc itself. To get around this, we can recreate our SQL string manually. But we've got to play it safe, there be SQL injection risks here.

Safe string formatting

It's important to escape and quote strings properly when manually constructing SQL strings, here's how you can do it:

import pyodbc
from urllib.parse import quote_plus

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=path_to_your_db.accdb;'
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

def safe_format_query(sql, params):
    placeholders = sql.count('?')
    safe_params = tuple(quote_plus(str(p)) for p in params)
    assert placeholders == len(safe_params), "SQL parameter surprise party! Too many guests"
    query = sql % safe_params
    return query

# Use it like this:
query = "SELECT * FROM Users WHERE Name = ? AND Age > ?"
params = ("O'Reilly", 21)
print(safe_format_query(query, params))

This piece of code will construct an SQL string that replaces the parameters safely, making sure it doesn't invite unwanted SQL injections over for a party.

Exception handling: your silver bullet against bugs

When your SQL queries are out in the wild, they might encounter all sorts of strange and wonderful edge cases. So instead of just hoping for the best - as my dating coach keeps telling me - it's better to implement some robust exception handling for your SQL queries:

try:
    cursor.execute(sql, params)
except pyodbc.Error as e:
    error_message = str(e)
    # Log the failed party invitation and who weren't invited
    mock_query(sql, params)
    raise

In this way, you capture any errors that occur during query execution and even log the query, so you know exactly which query smoked your server.

A tour to MS-Access Land

Each database has its own peculiarities (aren't we all special), and MS-Access is no different. Understanding how MS-Access handles prepared statements improves your debugging efforts. MS-Access might surprise you with different use of wildcard characters or perhaps a peculiar join syntax. But hey, it's all part of the journey (or so my therapist keeps telling me).