Prevent SQL Injection with Prepared Statements in Python Flask (SQLite)

from flask import Flask, request, jsonify, g
import sqlite3

app = Flask(__name__)
DATABASE = 'database.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row # Allows accessing columns by name
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

def init_db():
    with app.app_context():
        db = get_db()
        cursor = db.cursor()
        cursor.execute('DROP TABLE IF EXISTS users')
        cursor.execute('''
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                email TEXT NOT NULL
            )
        ''')
        db.commit()

# Run once to initialize the database
# init_db()

@app.route('/register', methods=['POST'])
def register_user():
    username = request.json.get('username')
    email = request.json.get('email')

    if not username or not email:
        return jsonify({'error': 'Username and email are required'}), 400

    db = get_db()
    cursor = db.cursor()
    try:
        # PREPARED STATEMENT: Use placeholders (?) and pass parameters separately
        cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", (username, email))
        db.commit()
        return jsonify({'message': 'User registered successfully'}), 201
    except sqlite3.IntegrityError:
        return jsonify({'error': 'Username or email already exists'}), 409
    except Exception as e:
        db.rollback()
        return jsonify({'error': str(e)}), 500

@app.route('/user/<username>', methods=['GET'])
def get_user_by_username():
    username = request.view_args['username'] # Get username from URL path
    
    db = get_db()
    cursor = db.cursor()
    # PREPARED STATEMENT for SELECT: Again, use placeholder
    cursor.execute("SELECT id, username, email FROM users WHERE username = ?", (username,))
    user = cursor.fetchone()
    
    if user:
        return jsonify(dict(user)) # Convert sqlite3.Row to dict
    return jsonify({'message': 'User not found'}), 404

if __name__ == '__main__':
    # Call init_db() uncommented only once to set up the database
    # init_db()
    app.run(debug=True, port=5000)