SQL Injection in TurboGears typically occurs when developers bypass the SQLAlchemy ORM to run raw SQL using string interpolation. Whether handling legacy SQLObject/SQLAlchemy 1.x or modern SQLAlchemy 2.0 sessions, the fix is universal: stop concatenating user input and start using bound parameters or the ORM's abstraction layer.

The Vulnerable Pattern

from tg import request
from myapp.model import DBSession
VULNERABLE: Direct string formatting into raw SQL via DBSession.execute
This allows an attacker to break out of the query using: ’ OR ‘1’=‘1
username = request.params.get(‘user’)
query = “SELECT * FROM users WHERE username = ‘%s’” % username
result = DBSession.execute(query)

The Secure Implementation

The vulnerability stems from treating untrusted user input as executable SQL code. In the vulnerable snippet, Python's string formatting (%) replaces the placeholder before the database sees the query. In the secure snippets, we leverage SQLAlchemy's underlying DB-API implementation. The ORM (Option 1) abstracts the query building entirely, ensuring input is treated as a literal. The parameterized raw SQL (Option 2) sends the query template and the data separately to the database engine, making it mathematically impossible for the input to alter the query logic.

from tg import request
from myapp.model import DBSession, User
from sqlalchemy import text
SECURE OPTION 1: Modern ORM (Preferred)
The ORM automatically handles parameterization
username = request.params.get(‘user’)
user = DBSession.query(User).filter(User.username == username).first()
SECURE OPTION 2: Parameterized Raw SQL
Use sqlalchemy.text() with a dictionary for bound parameters
query = text(“SELECT * FROM users WHERE username = :u”)
result = DBSession.execute(query, {‘u’: username})