SQL Injection and Security

🎯 Life Analogy

Imagine a package pickup point:

Normal flow: You say "Pick up Zhang San's package," and the staff searches by name → normal query
Injection attack: You say "Pick up Zhang San's package, and also give me Li Si's," and the staff doesn't carefully distinguish, giving you both packages → SQL injection
Defense measures: The staff requires you to fill out a complete pickup form, with each field filled in separately, not accepting verbally concatenated requests → parameterized query

📚 Core Concepts

1. SQL Injection Principle

The essence of SQL injection is: user input is executed as SQL code.

When a program constructs SQL using string concatenation, attackers can insert malicious SQL fragments.

SQL

-- Backend code (pseudocode)
sql = "SELECT * FROM users WHERE username = '" + input_user + "' AND password = '" + input_pass + "'"

-- Normal input
-- input_user = "admin"
-- input_pass = "123456"
-- Generated SQL:
SELECT * FROM users WHERE username = 'admin' AND password = '123456'

-- Malicious input
-- input_user = "admin' --"
-- input_pass = "anything"
-- Generated SQL:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'
-- '--' is a SQL comment, the rest of the condition is commented out, bypassing password verification!

2. Common Injection Attack Techniques

SQL

-- 1. Bypass login verification
-- Input: admin' OR '1'='1' --
SELECT * FROM users WHERE username = 'admin' OR '1'='1' --' AND password = ''

-- 2. UNION query to steal data
-- Input: ' UNION SELECT username, password FROM users --
SELECT * FROM products WHERE name = '' UNION SELECT username, password FROM users --'

-- 3. Stacked queries to execute multiple statements
-- Input: '; DROP TABLE users; --
SELECT * FROM products WHERE name = ''; DROP TABLE users; --'

-- 4. Boolean blind injection (infer data through response differences)
-- Input: ' AND (SELECT SUBSTRING(username,1,1) FROM users LIMIT 1)='a' --
SELECT * FROM products WHERE name = '' AND (SELECT SUBSTRING(username,1,1) FROM users LIMIT 1)='a' --'

3. Parameterized Query (Prepared Statement)

Core idea: SQL structure and data are separated; the database engine will not execute parameter values as SQL code.

SQL

-- ❌ String concatenation (dangerous)
SET @username = "admin' --";
SET @sql = CONCAT('SELECT * FROM users WHERE username = ''', @username, '''');
PREPARE stmt FROM @sql;
EXECUTE stmt;

-- ✅ Parameterized query (safe)
SET @username = "admin' --";
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;
-- The database treats the entire @username as a single string value, not executing any SQL fragments within it

Parameterized queries in various languages:

PYTHON

# Python + MySQL Connector
cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))

# Python + SQLAlchemy ORM
user = session.query(User).filter(User.username == username, User.password == password).first()

JAVASCRIPT

// Node.js + mysql2
const [rows] = await pool.execute('SELECT * FROM users WHERE username = ? AND password = ?', [username, password]);

// Node.js + Prisma ORM
const user = await prisma.user.findFirst({ where: { username, password } });

JAVA

// Java JDBC
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE username = ? AND password = ?");
ps.setString(1, username);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();

PHP

// PHP PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);

4. ORM Injection Prevention

ORM (Object-Relational Mapping) frameworks typically use parameterized queries automatically, but caution is still needed when concatenating raw SQL:

PYTHON

# ✅ ORM automatic parameterization (safe)
User.objects.filter(username=username, password=password)

# ❌ Concatenating raw SQL in ORM (dangerous)
User.objects.raw(f"SELECT * FROM users WHERE username = '{username}'")

# ✅ Safe way to use raw SQL in ORM
User.objects.raw("SELECT * FROM users WHERE username = %s", [username])

JAVASCRIPT

// ✅ Sequelize ORM automatic parameterization
await User.findOne({ where: { username, password } });

// ❌ Sequelize concatenated query (dangerous)
await sequelize.query(`SELECT * FROM users WHERE username = '${username}'`);

// ✅ Sequelize safe approach
await sequelize.query("SELECT * FROM users WHERE username = ?", { replacements: [username] });

5. Principle of Least Privilege

Database users should only be granted the minimum privileges needed to complete their tasks:

SQL

-- Create read-only user
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON mydb.* TO 'app_readonly'@'%';

-- Create application user (only allow CRUD, no DROP/ALTER)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';

-- Prevent privilege propagation
REVOKE GRANT OPTION ON mydb.* FROM 'app_user'@'%';

-- Admin user (internal management only)
CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'very_strong_password';
GRANT ALL PRIVILEGES ON mydb.* TO 'db_admin'@'localhost';

6. Defense Checklist

Defense Measure	Description
✅ Parameterized queries	Use parameter placeholders for all SQL queries
✅ ORM frameworks	Prefer ORM to reduce hand-written SQL
✅ Input validation	Validate length, type, format (whitelist over blacklist)
✅ Least privilege	Grant only necessary permissions to database users
✅ Error messages	Don't expose database error details to users
✅ WAF protection	Deploy a Web Application Firewall
✅ Regular audits	Check code for SQL concatenation
❌ String escaping	Not reliable enough, should not be the sole defense

💡 Basic Syntax

SQL

-- MySQL Prepared Statement
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

-- Safe approach for MySQL LIKE queries
PREPARE stmt FROM 'SELECT * FROM users WHERE name LIKE ?';
SET @pattern = CONCAT('%', 'keyword', '%');
EXECUTE stmt USING @pattern;

-- Safe approach for IN clauses (requires dynamic construction)
-- Method: Use FIND_IN_SET or a temporary table
PREPARE stmt FROM 'SELECT * FROM users WHERE FIND_IN_SET(id, ?)';
SET @ids = '1,2,3';
EXECUTE stmt USING @ids;

💡 Tip: Never trust user input. Even dropdown menus and hidden fields can be tampered with; the server must validate and parameterize all inputs.

Before (dangerous):

PYTHON

# Python Flask - dangerous approach
@app.route('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = request.form['password']
    # ❌ String concatenation, vulnerable to SQL injection
    sql = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
    user = db.execute(sql).fetchone()
    if user:
        return "Login successful"
    return "Invalid username or password"

▶ Try it Yourself

After (safe):

PYTHON

# Python Flask - safe approach
@app.route('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = request.form['password']

    # Input validation
    if not username or len(username) > 50:
        return "Invalid username format", 400
    if not password or len(password) > 100:
        return "Invalid password format", 400

    # ✅ Parameterized query
    user = db.execute(
        "SELECT * FROM users WHERE username = ? AND password = ?",
        (username, password)
    ).fetchone()

    if user:
        return "Login successful"
    return "Invalid username or password"

Example: Securing Search with Injection Prevention and Performance Optimization (Difficulty ⭐⭐)

Dangerous approach:

JAVASCRIPT

// Node.js Express - dangerous approach
app.get('/search', (req, res) => {
    const keyword = req.query.q;
    // ❌ SQL concatenation, vulnerable to injection
    const sql = `SELECT * FROM articles WHERE title LIKE '%${keyword}%' OR content LIKE '%${keyword}%'`;
    db.query(sql, (err, results) => {
        res.json(results);
    });
});

▶ Try it Yourself

Safe + optimized approach:

JAVASCRIPT

// Node.js Express - safe + optimized approach
app.get('/search', async (req, res) => {
    try {
        const keyword = req.query.q?.trim();

        // Input validation
        if (!keyword || keyword.length > 100) {
            return res.status(400).json({ error: 'Search keyword must be 1-100 characters' });
        }

        // ✅ Parameterized query + full-text index optimization
        const sql = `
            SELECT id, title, LEFT(content, 200) AS snippet, created_at
            FROM articles
            WHERE status = 1
              AND MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
            ORDER BY MATCH(title, content) AGAINST(? IN BOOLEAN MODE) DESC
            LIMIT 20
        `;
        const [rows] = await pool.execute(sql, [keyword, keyword]);
        res.json(rows);
    } catch (err) {
        // Don't expose database error details
        console.error('Search error:', err);
        res.status(500).json({ error: 'Search service temporarily unavailable' });
    }
});

🏢 Scenario 1: User Registration Endpoint Security

PYTHON

# Python - Secure user registration
import re
import hashlib

@app.route('/register', methods=['POST'])
def register():
    username = request.form.get('username', '').strip()
    email = request.form.get('email', '').strip()
    password = request.form.get('password', '')

    # 1. Input validation (whitelist)
    if not re.match(r'^[a-zA-Z0-9_]{3,50}$', username):
        return "Username can only contain letters, numbers, and underscores, 3-50 characters", 400
    if not re.match(r'^[^@]+@[^@]+\.[^@]+$', email) or len(email) > 100:
        return "Invalid email format", 400
    if len(password) < 8 or len(password) > 100:
        return "Password must be 8-100 characters", 400

    # 2. Parameterized query to check existence
    existing = db.execute(
        "SELECT id FROM users WHERE username = ? OR email = ?",
        (username, email)
    ).fetchone()
    if existing:
        return "Username or email already registered", 409

    # 3. Password hash storage
    password_hash = hashlib.sha256(password.encode()).hexdigest()

    # 4. Parameterized insert
    db.execute(
        "INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)",
        (username, email, password_hash)
    )
    db.commit()

    return "Registration successful", 201

🏢 Scenario 2: Safe Construction of Dynamic Filter Conditions

JAVASCRIPT

// Node.js - Safe dynamic query construction
function buildProductQuery(filters) {
    let sql = 'SELECT * FROM products WHERE 1=1';
    const params = [];

    if (filters.categoryId) {
        sql += ' AND category_id = ?';
        params.push(filters.categoryId);
    }

    if (filters.minPrice !== undefined) {
        sql += ' AND price >= ?';
        params.push(filters.minPrice);
    }

    if (filters.maxPrice !== undefined) {
        sql += ' AND price <= ?';
        params.push(filters.maxPrice);
    }

    if (filters.keyword) {
        sql += ' AND name LIKE ?';
        params.push(`%${filters.keyword}%`);
    }

    if (filters.status !== undefined) {
        sql += ' AND status = ?';
        params.push(filters.status);
    }

    // Sort field whitelist
    const allowedSort = ['price', 'created_at', 'sales_count'];
    const sortBy = allowedSort.includes(filters.sortBy) ? filters.sortBy : 'created_at';
    const sortOrder = filters.sortOrder === 'ASC' ? 'ASC' : 'DESC';
    sql += ` ORDER BY ${sortBy} ${sortOrder}`;

    // Pagination parameter validation
    const page = Math.max(1, parseInt(filters.page) || 1);
    const pageSize = Math.min(100, Math.max(1, parseInt(filters.pageSize) || 20));
    sql += ' LIMIT ? OFFSET ?';
    params.push(pageSize, (page - 1) * pageSize);

    return { sql, params };
}

// Usage
app.get('/products', async (req, res) => {
    const { sql, params } = buildProductQuery(req.query);
    const [rows] = await pool.execute(sql, params);
    res.json(rows);
});

💡 Key: Sort fields cannot use parameterized placeholders directly (since they are column names, not values) and must be validated with a whitelist.

❓ FAQ

Q: Do parameterized queries affect performance? A: No. Parameterized queries can also leverage the database's precompiled cache, making repeated executions of the same query template faster.

Q: Are ORM frameworks always safe? A: Not necessarily. ORM's automatic parameterization is safe, but if you use raw() or string concatenation to construct queries, injection risk still exists. Frameworks are just tools; security awareness is what matters.

Q: Can stored procedures prevent injection? A: Stored procedures themselves don't prevent injection. If a stored procedure uses dynamic SQL concatenation internally, it's equally vulnerable. The key is whether parameterization is used.

Q: Is front-end validation alone sufficient? A: Absolutely not. Front-end validation can be bypassed (directly calling APIs, modifying requests). All security validation must be performed on the server side.

📖 Summary

This lesson deeply covered SQL injection attacks and defenses:

Injection principle: User input is executed as SQL code, rooted in string concatenation
Attack techniques: Login bypass, UNION queries, stacked queries, blind injection, etc.
Core defense: Parameterized queries (Prepared Statements), separating SQL structure from data
ORM frameworks: Automatic parameterization, but still vigilant when concatenating raw SQL
Least privilege: Grant only necessary permissions to database users; never use root for application connections
Defense in depth: Parameterization + input validation + access control + error hiding + WAF

📝 Exercises

Find the SQL injection vulnerability in the following code and fix it:

SQL

query = "SELECT * FROM products WHERE category = '" + category + "' ORDER BY " + sortField

Write secure parameterized query code for a user search feature (supporting fuzzy search, pagination, and sorting).
Create three database users for your project: a read-only user, an application user, and an admin user, granting appropriate permissions to each.

Next Lesson →27-optimization.md

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

SQL Injection and Security

SQL Injection and Security

🎯 Life Analogy

📚 Core Concepts

1. SQL Injection Principle

2. Common Injection Attack Techniques

3. Parameterized Query (Prepared Statement)

4. ORM Injection Prevention

5. Principle of Least Privilege

6. Defense Checklist

💡 Basic Syntax

Example: Securing a Login Endpoint Against Injection (Difficulty ⭐)

Example: Securing Search with Injection Prevention and Performance Optimization (Difficulty ⭐⭐)

🏢 Scenario 1: User Registration Endpoint Security

🏢 Scenario 2: Safe Construction of Dynamic Filter Conditions

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好