SQL injection prevention in Flask - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is SQL injection?

SQL injection is a security problem where an attacker adds harmful SQL code into input fields to trick the database into doing things it shouldn't, like revealing or changing data.

beginner

How does using parameterized queries help prevent SQL injection in Flask?

Parameterized queries keep user input separate from the SQL code. This means the database treats input only as data, not as commands, stopping attackers from changing the query's meaning.

intermediate

What Flask extension is commonly used to safely interact with databases and prevent SQL injection?

Flask-SQLAlchemy is a popular extension that helps write safe database queries using an object-oriented style, which automatically handles input safely to avoid SQL injection.

beginner

Why should you avoid building SQL queries by directly adding user input strings in Flask?

Directly adding user input to SQL queries can let attackers insert harmful commands. This is unsafe because the database runs whatever code it receives, including bad commands.

intermediate

What is an example of a safe way to query a database in Flask using parameterized queries?

Using Flask's database cursor with placeholders like: cursor.execute('SELECT * FROM users WHERE name = %s', (username,)) safely inserts the username without risk of injection.

What is the main risk of SQL injection?

A Attackers can run harmful SQL commands through user input

B The database runs faster

C User input is ignored

D The website loads slower

SQL injection lets attackers add harmful commands that the database will run.

Which method helps prevent SQL injection in Flask?

A Using plain text files instead of databases

B Concatenating strings to build queries

C Ignoring user input

D Using parameterized queries with placeholders

Parameterized queries keep user input separate from SQL code, preventing injection.

What does Flask-SQLAlchemy provide to help with SQL injection?

A A way to write raw SQL strings only

B An object-oriented way to build safe queries

C A tool to speed up the database

D A method to disable user input

Flask-SQLAlchemy lets you write queries as Python objects, which safely handle input.

Why is directly adding user input to SQL queries dangerous?

A It makes the code shorter

B It improves database security

C It can let attackers change the query meaning

D It prevents errors

Direct input can let attackers insert harmful SQL commands.

Which of these is a safe example of querying a database in Flask?

A cursor.execute('SELECT * FROM users WHERE name = %s', (username,))

B cursor.execute(f'SELECT * FROM users WHERE name = '{username}'')

C cursor.execute('SELECT * FROM users WHERE name = ' + username)

D cursor.execute('SELECT * FROM users')

Using placeholders (%s) with parameters safely inserts user input.

Explain how parameterized queries prevent SQL injection in Flask applications.

Think about how the database sees the input versus the command.

Describe why directly inserting user input into SQL queries is unsafe and how Flask-SQLAlchemy helps avoid this problem.

Consider how Flask-SQLAlchemy changes the way queries are written.

Practice

1. What is the main purpose of using parameterized queries in Flask when working with databases?

easy

A. To speed up database queries by caching results

B. To prevent SQL injection attacks by separating code from data

C. To allow users to write their own SQL commands

D. To automatically create database tables

Solution

1. Step 1: Understand SQL injection risk

   SQL injection happens when user input is treated as code, allowing attackers to change database commands.

2. Step 2: Role of parameterized queries

   Parameterized queries keep user data separate from SQL code, stopping attackers from injecting harmful commands.

3. Final Answer:

   To prevent SQL injection attacks by separating code from data -> Option B

4. Quick Check:

   SQL injection prevention = parameterized queries [OK]

Hint: Remember: separate data from code to stop injection [OK]

Common Mistakes:

• Thinking parameterized queries speed up queries
• Believing users can safely write SQL commands
• Confusing parameterized queries with table creation

2. Which of the following is the correct way to use parameterized queries with Flask's database cursor?

easy

A. cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

B. cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

C. cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

D. cursor.execute("SELECT * FROM users WHERE id = :id", user_id)

Solution

1. Step 1: Identify correct placeholder syntax

   In Flask with many DB adapters like psycopg2, %s is used as placeholder for parameters.

2. Step 2: Check parameter passing format

   Parameters must be passed as a tuple, e.g., (user_id,). cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) uses %s and tuple correctly.

3. Final Answer:

   cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) -> Option C

4. Quick Check:

   Use %s with tuple for parameters [OK]

Hint: Use %s and tuple for parameters in execute() [OK]

Common Mistakes:

• Using f-strings directly with user input
• Using ? placeholder which is for SQLite, not psycopg2
• Passing parameters incorrectly without tuple

3. Given this Flask code snippet, what will be the output if user_id = "1 OR 1=1" and parameterized queries are used correctly?

cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
results = cursor.fetchall()
print(results)

medium

A. Only rows where id equals the string '1 OR 1=1' will be returned

B. All rows from users table will be returned

C. Syntax error due to SQL injection

D. No rows will be returned because of wrong query

Solution

1. Step 1: Understand parameterized query effect

   Parameterized queries treat user input as data, not code, so "1 OR 1=1" is a string value, not SQL logic.

2. Step 2: Result of query with injected string

   The query looks for rows where id equals the string "1 OR 1=1", which likely matches no rows or only exact matches.

3. Final Answer:

   Only rows where id equals the string '1 OR 1=1' will be returned -> Option A

4. Quick Check:

   Parameterized input treated as data = Only rows where id equals the string '1 OR 1=1' will be returned [OK]

Hint: Parameterized input is data, not code, so no injection [OK]

Common Mistakes:

• Assuming injection runs and returns all rows
• Expecting syntax error from parameterized query
• Thinking query fails due to input format

4. Identify the error in this Flask code that tries to prevent SQL injection:

user_id = request.args.get('id')
cursor.execute("SELECT * FROM users WHERE id = %s" % user_id)
results = cursor.fetchall()

medium

A. Using wrong placeholder %s instead of ?

B. Missing parentheses in fetchall()

C. Not converting user_id to integer

D. Using string formatting instead of parameterized query

Solution

1. Step 1: Check how query is constructed

   The code uses string formatting (% operator) to insert user_id directly into the query string.

2. Step 2: Why this is wrong for injection prevention

   Direct string formatting allows malicious input to change SQL code, causing SQL injection risk.

3. Final Answer:

   Using string formatting instead of parameterized query -> Option D

4. Quick Check:

   Use parameterized queries, not string formatting [OK]

Hint: Never build queries with string formatting; use parameters [OK]

Common Mistakes:

• Thinking fetchall() needs parentheses fix
• Believing user_id must be int for injection safety
• Confusing %s and ? placeholders

5. You want to safely insert multiple user records into a database using Flask and prevent SQL injection. Which approach correctly combines parameterized queries with batch insertion?

users = [("alice", 25), ("bob", 30)]
cursor.executemany(??? , users)

What should replace ????

hard

A. "INSERT INTO users (name, age) VALUES (%s, %s)"

B. "INSERT INTO users (name, age) VALUES ('%s', '%s')"

C. "INSERT INTO users (name, age) VALUES (?, ?)"

D. "INSERT INTO users (name, age) VALUES (:name, :age)"

Solution

1. Step 1: Identify correct placeholder syntax for batch insert

   For executemany with psycopg2 or similar, %s placeholders are used without quotes.

2. Step 2: Check placeholder usage in options

   "INSERT INTO users (name, age) VALUES (%s, %s)" uses %s placeholders correctly; "INSERT INTO users (name, age) VALUES ('%s', '%s')" wrongly adds quotes, "INSERT INTO users (name, age) VALUES (?, ?)" uses ? (SQLite style), "INSERT INTO users (name, age) VALUES (:name, :age)" uses named placeholders which need dicts.

3. Final Answer:

   "INSERT INTO users (name, age) VALUES (%s, %s)" -> Option A

4. Quick Check:

   Use %s placeholders without quotes for executemany [OK]

Hint: Use %s placeholders without quotes for batch inserts [OK]

Common Mistakes:

• Adding quotes around %s placeholders
• Using ? placeholders with psycopg2
• Using named placeholders without dict parameters