SQL injection prevention in Flask - Mini Project: Build & Apply

Prevent SQL Injection in a Flask App

📖 Scenario: You are building a simple Flask web app that lets users search for books by title.To keep the app safe, you want to prevent SQL injection attacks.

🎯 Goal: Build a Flask app that safely queries a SQLite database using parameterized queries to prevent SQL injection.

📋 What You'll Learn

Create a SQLite database connection

Write a SQL query with a parameter placeholder

Use Flask request to get user input

Use parameterized queries to safely pass user input

Return query results in the Flask route

💡 Why This Matters

🌍 Real World

Web apps often take user input to query databases. Preventing SQL injection keeps user data and servers safe.

💼 Career

Understanding how to safely query databases is essential for backend web developers and security-conscious programmers.

1

Set up the Flask app and database connection

Import Flask and sqlite3. Create a Flask app called app. Define a function get_db_connection() that returns a connection to a SQLite database file named books.db.

Flask

# Your code here

Need a hint?

Use Flask(__name__) to create the app. Use sqlite3.connect with the exact filename 'books.db'.

2

Add a route to get the search term from the user

Import request from flask. Add a route /search with a function search(). Inside search(), get the query parameter title from request.args and store it in a variable called title.

Flask

from flask import Flask, request
import sqlite3

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('books.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/search')
def search():
    # Your code here

Need a hint?

Use request.args.get('title') to get the search term from the URL query string.

3

Query the database safely using parameterized queries

Inside the search() function, get a database connection by calling get_db_connection(). Use conn.execute() with a SQL query string "SELECT * FROM books WHERE title LIKE ?" and pass a tuple with f"%{title}%" as the parameter. Store the result in a variable called books. Then fetch all rows with books.fetchall().

Flask

from flask import Flask, request
import sqlite3

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('books.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/search')
def search():
    title = request.args.get('title')
    # Your code here

Need a hint?

Use a question mark ? as a placeholder in the SQL query. Pass the parameter as a tuple with f"%{title}%" to search titles containing the input.

4

Return the search results as a simple string

Still inside search(), close the database connection with conn.close(). Return a string that joins all book titles separated by commas using ','.join(book['title'] for book in books).

Flask

from flask import Flask, request
import sqlite3

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('books.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/search')
def search():
    title = request.args.get('title')
    conn = get_db_connection()
    books = conn.execute("SELECT * FROM books WHERE title LIKE ?", (f"%{title}%",))
    books = books.fetchall()
    # Your code here

Need a hint?

Close the connection before returning. Use a generator expression to join titles with commas.