Opening Thoughts

Hello friends, today let's discuss a crucial topic - SQL injection attacks. As someone who has been programming in Python for 5 years, I've seen many developers stumble on this issue. You might think: "It's just a database query, what's the big deal?" But this kind of thinking has gotten many people into trouble.

Let's use an analogy - a database is like your home safe, and SQL statements are like the combination to open it. If you carelessly reveal the combination rules to others, your property becomes vulnerable to theft. The same principle applies to SQL injection attacks.

Understanding SQL Injection

When it comes to SQL injection, you need to understand how dangerous it can be. I remember last year when a student encountered this - his website was hacked through SQL injection, and the entire user database was downloaded. What does this mean? Personal information of tens of thousands of users was leaked.

Let's look at a basic example. What do you think is wrong with this code:

def verify_user(username, password):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    query = f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
    cursor.execute(query)
    return cursor.fetchone() is not None

This code is used to verify user login, seems simple right? But if someone enters this username: ' OR '1'='1, the entire SQL statement becomes:

SELECT * FROM users WHERE username='' OR '1'='1' AND password='whatever'

Since '1'='1' is always true, this bypasses password verification. This is just the simplest attack method - real hackers can do much more.

Understanding Attack Principles

Let's analyze more deeply how SQL injection occurs. Imagine you're developing a library management system and need to query book information by title:

def search_books(book_name):
    conn = sqlite3.connect('library.db')
    cursor = conn.cursor()
    query = f"""
    SELECT title, author, publish_date 
    FROM books 
    WHERE title LIKE '%{book_name}%'
    """
    cursor.execute(query)
    return cursor.fetchall()

This code seems fine, but if someone inputs: %' UNION SELECT username, password, email FROM users; --, the final SQL statement becomes:

SELECT title, author, publish_date 
FROM books 
WHERE title LIKE '%%' UNION SELECT username, password, email FROM users; --%'

This allows attackers to obtain all information from the users table. When I was a technical consultant at a company, I encountered such a case - an intern accidentally discovered this vulnerability in a system written by a junior programmer.

Prevention Methods

After discussing these dangers, how do we prevent them? I've summarized several key points:

Parameterized Queries

First and most importantly, use parameterized queries. Here's the correct way:

def safe_verify_user(username, password):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    query = "SELECT * FROM users WHERE username=? AND password=?"
    cursor.execute(query, (username, password))
    return cursor.fetchone() is not None

The question marks (?) are placeholders that tell the database driver: "This is a parameter, please handle it correctly." The database driver automatically handles all escaping and quotation issues.

Using ORM Frameworks

Going further, I recommend using an ORM framework. SQLAlchemy is a great choice:

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    password = Column(String)

engine = create_engine('sqlite:///users.db')
Session = sessionmaker(bind=engine)

def verify_user_orm(username, password):
    session = Session()
    user = session.query(User).filter(
        User.username == username,
        User.password == password
    ).first()
    session.close()
    return user is not None

Using ORM not only prevents SQL injection but also makes the code more Pythonic. I've always used SQLAlchemy in real projects and never had security issues.

Input Validation

Besides parameterized queries and ORM, input validation is also important. I wrote a simple validation decorator:

import re
from functools import wraps

def validate_input(pattern):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            for arg in args:
                if isinstance(arg, str) and not re.match(pattern, arg):
                    raise ValueError("Invalid input detected")
            for value in kwargs.values():
                if isinstance(value, str) and not re.match(pattern, value):
                    raise ValueError("Invalid input detected")
            return func(*args, **kwargs)
        return wrapper
    return decorator

@validate_input(r'^[a-zA-Z0-9_]+$')
def process_username(username):
    # Business logic for processing username
    pass

This decorator ensures all inputs match our expected format. You can modify the regex pattern according to your needs.

Practical Example

Let's look at a complete practical example. Suppose we're developing a blog system:

from sqlalchemy import create_engine, Column, String, Integer, Text, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import datetime
import bleach

Base = declarative_base()

class BlogPost(Base):
    __tablename__ = 'blog_posts'
    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    content = Column(Text)
    author = Column(String(50))
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

engine = create_engine('sqlite:///blog.db')
Session = sessionmaker(bind=engine)

def create_post(title, content, author):
    # Clean input content
    clean_title = bleach.clean(title)
    clean_content = bleach.clean(content)
    clean_author = bleach.clean(author)

    session = Session()
    try:
        post = BlogPost(
            title=clean_title,
            content=clean_content,
            author=clean_author
        )
        session.add(post)
        session.commit()
        return True
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def get_posts_by_author(author):
    session = Session()
    try:
        posts = session.query(BlogPost).filter(
            BlogPost.author == author
        ).all()
        return posts
    finally:
        session.close()

This example demonstrates several important security practices: 1. Using ORM to prevent SQL injection 2. Using the bleach library to clean input content and prevent XSS attacks 3. Proper session management 4. Exception handling and resource cleanup

Summary and Recommendations

After this detailed explanation, you should have a deep understanding of SQL injection attacks. Remember these core principles: 1. Never directly concatenate SQL strings 2. Use parameterized queries or ORM frameworks 3. Validate and clean all user inputs 4. Regularly update dependency libraries 5. Implement proper exception handling and logging

Finally, here's a tip - use SQLite database for testing during development. Since SQLite is a file-based database, even if SQL injection occurs, it won't affect the production environment. Once the code is stable, migrate to MySQL or PostgreSQL in production.

Do you think these security measures are sufficient? Feel free to share your experiences and thoughts in the comments. If you have other security-related questions, you can also leave them for discussion. Next time we'll talk about other security programming topics in Python, such as password storage and encryption algorithms, stay tuned.