SQL injection prevention in Flask

Introduction

SQL injection is a security risk where bad users can change your database commands. Preventing it keeps your data safe and your app working right.

When your Flask app takes user input to search or filter database records.

When users log in and you check their username and password in the database.

When you add or update data in the database based on user forms.

When you build any feature that runs SQL queries using user data.

Syntax

Flask

cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

Use parameterized queries to safely insert user data into SQL commands.

Never build SQL commands by joining strings with user input directly.

Examples

This safely searches for a user by username using a parameter.

Flask

cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

This safely adds a new user with name and email from user input.

Flask

cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))

This safely updates a user's email by their ID.

Flask

cursor.execute("UPDATE users SET email = %s WHERE id = %s", (email, user_id))

Sample Program

This Flask app has a route to find a user by username safely. It uses a parameterized query to avoid SQL injection.

Flask

from flask import Flask, request
import psycopg2

app = Flask(__name__)

conn = psycopg2.connect(dbname='testdb', user='user', password='pass', host='localhost')

@app.route('/find_user')
def find_user():
    username = request.args.get('username', '')
    with conn.cursor() as cursor:
        cursor.execute("SELECT id, username FROM users WHERE username = %s", (username,))
        user = cursor.fetchone()
    if user:
        return f"User found: ID={user[0]}, Username={user[1]}"
    else:
        return "User not found"

if __name__ == '__main__':
    app.run(debug=True)

Important Notes

Always use parameterized queries or ORM methods that handle parameters for you.

Never trust user input; always treat it as unsafe until handled properly.

Test your app with special characters to ensure injection is blocked.

Summary

SQL injection lets attackers change your database commands.

Use parameterized queries in Flask with your database cursor to prevent it.

This keeps your app and data safe from bad users.