Fix SQL Injection (Legacy & Modern) in Bottle

SQLi in Bottle frameworks remains a top-tier exploit vector when developers treat 'request.params' as trusted data. Legacy codebases often sink user input directly into raw strings via f-strings or percent formatting, leading to full database compromise. Modern mitigation requires strict parameterization or the use of an abstraction layer like SQLAlchemy to decouple command logic from data.

The Vulnerable Pattern

from bottle import route, request
import sqlite3
@route(‘/api/user’)
def get_user_data():
user_id = request.query.id
db = sqlite3.connect(‘app.db’)
cursor = db.cursor()
# VULNERABLE: Direct string interpolation allows ‘1 OR 1=1’ payloads
query = f”SELECT username, email FROM users WHERE id = ‘{user_id}’”
cursor.execute(query)
return {“data”: cursor.fetchone()}

The Secure Implementation

The vulnerability stems from the database engine interpreting user-supplied strings as SQL commands. In the vulnerable snippet, an attacker can break out of the single quotes to execute arbitrary SQL. The secure implementation uses 'placeholders' (e.g., ?, %s, or :name). When using parameterization, the SQL template is sent to the DB engine first, and the data is sent separately. The engine never evaluates the data as code, effectively neutralizing the injection attempt. For modern Bottle apps, using an ORM like SQLAlchemy provides an additional layer of protection by default.

from bottle import route, request
import sqlite3
@route(‘/api/user’)
def get_user_data():
user_id = request.query.id
db = sqlite3.connect(‘app.db’)
cursor = db.cursor()
# SECURE: Using DB-API 2.0 parameterized queries
# The ’?’ placeholder ensures the driver treats input as a literal string
cursor.execute(“SELECT username, email FROM users WHERE id = ?”, (user_id,))
result = cursor.fetchone()
return {“data”: result} if result else {“error”: “Not found”}