SQL Injection is a common and dangerous security vulnerability in web applications. It occurs when an attacker is able to manipulate the SQL queries executed by an application, allowing them to retrieve, modify, or delete sensitive information from the database.

To prevent SQL Injection attacks in Python, we can utilize parameterized queries or prepared statements. These techniques ensure that user input is properly escaped or validated before being used in SQL queries.

1. Parameterized Queries

Parameterized queries separate the SQL code from the user-supplied input by using placeholders that are later filled with the sanitized user input. This prevents the attacker from modifying the structure of the query.

Here’s an example of using parameterized queries in Python with the sqlite3 module:

import sqlite3

def login(username, password):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))
    result = cursor.fetchone()

    conn.close()

    return result

In this example, the ? placeholders are used in the SQL query. The actual values for username and password are passed as a tuple as the second argument to execute().

2. Prepared Statements

Prepared statements work similarly to parameterized queries, but they are specifically supported by some database systems, such as PostgreSQL and MySQL. Prepared statements involve creating a template query that is sent to the database server, and then the user-supplied input is inserted later, automatically escaping any special characters.

Here’s an example of using prepared statements in Python with the psycopg2 module for PostgreSQL:

import psycopg2

def insert_user(username, password):
    conn = psycopg2.connect(host="localhost", database="mydb", user="myuser", password="mypassword")
    cursor = conn.cursor()

    query = "INSERT INTO users (username, password) VALUES (%s, %s)"
    data = (username, password)

    cursor.execute(query, data)
    conn.commit()

    conn.close()

In this example, the %s placeholders are used in the SQL query. The actual values for username and password are passed as a tuple as the second argument to execute().

3. Input Validation and Sanitization

Apart from using parameterized queries or prepared statements, it is also important to validate and sanitize user input before using it in SQL queries. This can help detect and block malicious input that might attempt to exploit SQL Injection vulnerabilities.

Here’s an example of validating and sanitizing user input in Python:

import re

def validate_username(username):
    if re.match("^[a-zA-Z0-9_]+$", username):
        return True
    else:
        return False

def sanitize_input(input_string):
    sanitized_string = re.sub("[^a-zA-Z0-9_\-@.]+", "", input_string)
    return sanitized_string

In this example, the validate_username() function checks if the username contains only alphanumeric characters, underscores, and does not contain any special characters. The sanitize_input() function removes any characters that are not alphanumeric, underscores, hyphens, ‘@’, or ‘.’

By validating and sanitizing user input, we can minimize the risk of SQL Injection attacks.

Remember that SQL Injection is just one of many security vulnerabilities to consider when developing web applications. Good security practices, such as regular updates and patches, secure coding techniques, and security testing, should be followed to ensure the overall security of the application.