CherryPy is a minimalist web framework that leaves database abstraction entirely to the developer. This flexibility is a double-edged sword: legacy implementations often rely on manual string formatting for SQL queries, creating textbook SQL injection vectors. To secure a CherryPy application, you must transition from raw string concatenation to parameterized queries (DB-API 2.0) or implement a modern ORM like SQLAlchemy.

The Vulnerable Pattern

import cherrypy
import sqlite3
class App:
@cherrypy.expose
def index(self, user_id):
conn = sqlite3.connect(‘data.db’)
cursor = conn.cursor()
# CRITICAL VULNERABILITY: String formatting allows an attacker to inject SQL
# Example exploit: /index?user_id=1’ OR ‘1’=‘1
query = f”SELECT username FROM users WHERE id = ‘{user_id}’”
cursor.execute(query)
result = cursor.fetchone()
return result[0] if result else ‘Not found’

The Secure Implementation

The vulnerability exists because the Python interpreter evaluates the f-string before the SQL engine sees it, merging untrusted input with the command logic. In the secure version, we use the '?' placeholder (or '%s' for PostgreSQL/MySQL). This tells the database driver to treat the input as a literal value. For modern CherryPy stacks, the best practice is to move away from raw DB-API calls and use SQLAlchemy, which provides a high-level abstraction that prevents injection by design while offering better session management via CherryPy tools.

import cherrypy
import sqlite3
class App:
@cherrypy.expose
def index(self, user_id):
conn = sqlite3.connect(‘data.db’)
cursor = conn.cursor()
# SECURE: Using DB-API 2.0 placeholders
# The driver handles escaping and ensures user_id is treated as data, not code
query = “SELECT username FROM users WHERE id = ?”
cursor.execute(query, (user_id,))
result = cursor.fetchone()
return result[0] if result else ‘Not found’
For Modern Implementations: Use SQLAlchemy
user = session.query(User).filter(User.id == user_id).first()