SQL Injection and Parameterized Queries

After completing this topic, you will be able to:

Explain how SQL injection works and write code that defends against it using parameterized queries.

What is SQL Injection?

SQL injection is an attack that manipulates a database by inserting SQL code into user input. It is one of the oldest, most dangerous, and still most common web security attacks.

How the Attack Works

Consider a login form. When a user enters an email, the server might process it like this:

// ❌ Dangerous code — directly inserting input into SQL
const email = req.body.email;
const sql = `SELECT * FROM users WHERE email = '${email}'`;
connection.query(sql);

Normal input: hoon@example.com

SELECT * FROM users WHERE email = 'hoon@example.com'
-- Works as expected

Malicious input: ' OR '1'='1

SELECT * FROM users WHERE email = '' OR '1'='1'
-- '1'='1' is always true → returns all user data!

Even more dangerous attack: '; DROP TABLE users; --

SELECT * FROM users WHERE email = ''; DROP TABLE users; --'
-- The semicolon ends the first query and executes the command to delete the table.
-- -- comments out the rest.

Key Point: The problem is that user input changes the structure of the SQL itself.

Defense: Parameterized Queries

Parameterized queries (also known as Prepared Statements) separate the SQL structure from the data.

// ✅ Safe code — using placeholders
const email = req.body.email;
connection.query(
  'SELECT * FROM users WHERE email = ?',
  [email]
);

Even if the malicious input ' OR '1'='1 is entered:

SELECT * FROM users WHERE email = '\' OR \'1\'=\'1'
-- The entire input is treated as a single string value. The SQL structure does not change.

The database engine first parses the SQL structure and then safely substitutes the values into the ? placeholders. No matter how strange the value, it will not be interpreted as SQL commands.

Language-Specific Parameterization Patterns

Node.js (mysql2)

// ? placeholder
connection.query('SELECT * FROM users WHERE id = ?', [userId]);
connection.query(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  [name, email]
);

Python (sqlite3 / psycopg2)

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

# %s placeholder (psycopg2 / MySQL Connector)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

Java (JDBC)

PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE id = ?"
);
stmt.setInt(1, userId);
ResultSet rs = stmt.executeQuery();

The syntax is different, but the principle is the same: separate SQL from data.

Additional Layers of Defense

Parameterized queries are essential, but additional layers of defense exist:

The most important thing: never directly concatenate user input into SQL strings. By following this one rule, SQL injection will not occur.