SQLViews, Procedures, and Securityadvanced26 min read

SQL Injection and Parameterized Queries

Understand SQL injection vulnerabilities and prevent them with parameterized queries, safe query construction, least-privilege database accounts, and secure backend practices.

In this lesson, you will learn

✓Explain how SQL injection occurs when untrusted input changes SQL query structure
✓Identify the risks of building SQL statements through string concatenation
✓Use parameterized queries in Node.js, Go, and Python
✓Distinguish query parameters from SQL identifiers such as table and column names
✓Apply input validation, least privilege, and safe error handling as additional security controls
✓Recognize common SQL injection mistakes and describe how to test for them safely

1. Introduction to SQL Injection

SQL injection is a security vulnerability that occurs when an application constructs a SQL statement using untrusted input in a way that allows the input to change the statement's intended structure.

Imagine a login form that accepts a username. The backend uses that value to construct a database query. If the application combines the input directly with SQL text, specially crafted input may change how the database interprets the statement.

The problem is not that SQL accepts user input. Applications must often search for usernames, products, and other user-supplied values. The problem is allowing input to become executable SQL syntax.

SQL injection can potentially expose confidential records, bypass application checks, modify data, or delete data, depending on the vulnerable query, database permissions, and driver behavior.

The most important prevention technique is to use parameterized queries so that SQL code and supplied values remain separate.

This lesson uses PostgreSQL examples and demonstrates backend patterns for Node.js, Go, and Python. The application examples require the relevant database driver to be installed.

2. How SQL Injection Changes Query Meaning

Consider a backend that searches for a user by username.

The intended query is:

SELECT user_id, username
FROM app_users
WHERE username = 'alice';

The application should treat alice as a value to search for.

A vulnerable implementation might construct SQL by concatenating a string supplied by the user into the query text. If the application does not safely separate that value from the SQL grammar, special characters and SQL operators may change the condition being evaluated.

For example, an input containing a quote can terminate a string literal in a vulnerable concatenated statement. An attacker may then attempt to add SQL syntax that changes the condition.

The exact result depends on how the application builds the query, which database engine it uses, and whether the driver permits the resulting statement.

The security principle is straightforward: never let an untrusted value control the SQL grammar through direct string concatenation.

A parameterized query allows the database driver to transmit the SQL statement and its parameter values separately. The supplied value is then treated as data, not as a new SQL expression.

3. Why String Concatenation Is Dangerous

This example demonstrates an unsafe pattern. The code is intentionally vulnerable and should never be used in a real application.

The application inserts the username directly into the SQL statement. That means the SQL text changes according to the supplied value.

Even if the code works correctly for ordinary input, it may behave unexpectedly or become exploitable when the input contains SQL syntax.

The correct fix is not to search for a better way to concatenate strings. The fix is to use the parameter-binding feature provided by the database driver.

Unsafe query construction

sql

1
2
3
4
5
6
7
8
-- Intended query for an ordinary username:
SELECT user_id, username
FROM app_users
WHERE username = 'alice';
 
-- A vulnerable application might build the query
-- by concatenating untrusted text into SQL.
-- Do not construct executable SQL this way.

Output

For an ordinary username, the query returns matching rows. The behavior of a vulnerable application can change when untrusted input alters the generated SQL text.

SQL injection is a query-construction vulnerability. The SQL shown here illustrates the intended query; unsafe concatenation happens in the application code that constructs the statement.

4. Parameterized Queries: The Correct Approach

A parameterized query uses placeholders for values. The application supplies the SQL statement and the values as separate inputs to the database driver.

For PostgreSQL, a parameterized statement can use numbered placeholders such as $1 and $2.

Example:

SELECT user_id, username
FROM app_users
WHERE username = $1;

The application supplies the username as the first parameter.

If the supplied value contains quotes, comment markers, or SQL keywords, those characters remain part of the value rather than being interpreted as new SQL syntax.

Benefits include:
- Separating SQL structure from data.
- Preventing user-supplied values from changing the query's grammar.
- Making code easier to review.
- Avoiding many quoting and escaping mistakes.

Parameterization does not automatically make every part of a query safe. SQL identifiers and keywords generally cannot be supplied through ordinary value placeholders. Dynamic identifiers require a separate safe-construction strategy.

5. PostgreSQL Parameterized Query Example

The following SQL statement shows the PostgreSQL placeholder syntax.

The $1 placeholder represents the first parameter supplied by the application driver. PostgreSQL SQL clients may support their own parameter-substitution conventions, so the exact syntax used to execute a parameterized statement depends on the client or driver.

For backend applications, use the driver's parameter-binding API rather than manually replacing placeholders with strings.

A parameterized PostgreSQL query

sql

1
2
3
SELECT user_id, username
FROM app_users
WHERE username = $1;

Output

The query returns the rows whose username matches the first bound parameter. The actual rows depend on the contents of app_users and the value supplied by the application.

The statement contains a placeholder rather than embedding the username directly in the SQL text. The application must bind the value using a PostgreSQL driver that supports this placeholder syntax.

6. Parameterized Queries in Node.js

In a Node.js backend using the pg package, pass the SQL statement and the parameter values separately.

Example:

const result = await pool.query(
'SELECT user_id, username FROM app_users WHERE username = $1',
[username]
);

The SQL statement contains $1. The array [username] supplies the corresponding value.

Do not create the SQL string by interpolating username into it.

The following example uses Express and pg-style parameter binding. It assumes that pool is an initialized PostgreSQL connection pool and that the app_users table exists.

Production applications should also use authentication, authorization, request validation, sensible error handling, and rate limiting where appropriate. Parameterized queries prevent SQL injection through bound values; they do not establish that a user is authorized to access the requested record.

Node.js with the pg driver

javascript

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
app.get("/users/search", async (req, res) => {
  const username = req.query.username;
 
  if (typeof username !== "string" || username.length === 0) {
    return res.status(400).json({
      error: "A username is required"
    });
  }
 
  try {
    const result = await pool.query(
      "SELECT user_id, username FROM app_users WHERE username = $1",
      [username]
    );
 
    return res.json(result.rows);
  } catch (error) {
    console.error("User search failed");
 
    return res.status(500).json({
      error: "An internal server error occurred"
    });
  }
});

Output

For a valid request, the endpoint returns a JSON array containing the matching rows. If the username is missing or invalid, it returns HTTP 400. If the database operation fails, it returns HTTP 500 without exposing database error details to the caller.

The username is passed as a bound value in the second argument to pool.query. The code does not concatenate the username into the SQL statement. Logging should be configured so sensitive values and credentials are not accidentally recorded.

7. Parameterized Queries in Go

Go's database/sql package provides methods such as QueryContext, QueryRowContext, and ExecContext.

For PostgreSQL drivers, placeholders commonly use $1, $2, and so on. Other database drivers may use a different placeholder format.

For example:

row := db.QueryRowContext(
ctx,
"SELECT user_id FROM app_users WHERE username = $1",
username,
)

The username is passed separately from the SQL text.

Use QueryRowContext when a query is expected to return one row. Use QueryContext when you need to process multiple rows. Always handle returned errors and close a rows iterator when using QueryContext.

Go with database/sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
row := db.QueryRowContext(
    ctx,
    "SELECT user_id FROM app_users WHERE username = $1",
    username,
)
 
var userID int
err := row.Scan(&userID)
 
if err != nil {
    if errors.Is(err, sql.ErrNoRows) {
        // Handle a username that does not exist.
    } else {
        // Handle a database error.
    }
}

Output

If a matching row exists, userID receives its identifier. If no row matches, Scan returns sql.ErrNoRows. Other database errors should be handled separately.

The SQL statement and username are passed as separate arguments. This example assumes ctx, db, username, and the required imports are already defined.

8. Parameterized Queries in Python

Python database drivers also support parameterized queries, but placeholder syntax varies by driver.

For example, psycopg uses %s placeholders for values in ordinary parameterized execute calls. This is not Python string formatting: the query and the parameter tuple are passed separately to the driver.

Correct pattern:

cursor.execute(
"SELECT user_id, username FROM app_users WHERE username = %s",
(username,)
)

The comma in (username,) makes this a one-element tuple.

Do not use Python's % operator, f-strings, or string concatenation to insert untrusted values directly into SQL statements.

The following example uses psycopg-style syntax and assumes that conn is an established connection and the required table exists.

Python with psycopg

python

1
2
3
4
5
6
7
8
9
10
with conn.cursor() as cursor:
    cursor.execute(
        "SELECT user_id, username FROM app_users WHERE username = %s",
        (username,)
    )
 
    rows = cursor.fetchall()
 
for row in rows:
    print(row)

Output

The code prints each matching row as a tuple. If no username matches, the result list is empty. The exact values depend on the database contents.

The value is bound separately using the driver's parameter API. The placeholder format is driver-specific; do not assume that every Python SQL library uses %s.

9. SQL Parameters Cannot Usually Replace Identifiers

A value parameter represents data, such as a username, product identifier, or date. It generally cannot replace a table name, column name, SQL keyword, or sort direction.

For example, this is not a valid way to choose a table:

SELECT * FROM $1;

A normal PostgreSQL value parameter cannot stand in for the table identifier.

Suppose a report endpoint lets a caller choose whether to sort products by name or price. Do not concatenate an arbitrary request value into ORDER BY.

Instead, map the request to a fixed allowlist of identifiers:

const sortColumns = {
name: "product_name",
price: "price"
};

Only allow supported keys, and construct the SQL identifier from that trusted mapping. Continue to bind all ordinary values through query parameters.

If dynamic identifiers are unavoidable, use the database driver's identifier-quoting facility where available, combined with an allowlist. Quoting protects identifier syntax, while an allowlist restricts which objects the application is willing to expose.

10. Dynamic SQL and Safe Identifier Handling

Dynamic SQL constructs a statement at runtime. It may be necessary when table names or column names genuinely vary, but it must be handled carefully.

In PostgreSQL PL/pgSQL, EXECUTE supports dynamic SQL. Use format('%I', identifier) to quote an SQL identifier, and use USING to pass data values.

For example:

EXECUTE format(
'SELECT count(*) FROM %I.%I WHERE status = $1',
schema_name,
table_name
)
INTO matching_count
USING requested_status;

The %I format specifier safely quotes an identifier. The $1 placeholder receives requested_status through USING.

Identifier quoting prevents syntax confusion, but it does not decide whether the caller should be allowed to query a particular table. Validate identifiers against an allowlist when the allowed object set is known.

Do not confuse %I with %L or a value placeholder. They serve different purposes.

Safe dynamic SQL in PL/pgSQL

sql

1
2
3
4
5
6
7
8
-- Illustrative fragment inside a PL/pgSQL routine:
EXECUTE format(
    'SELECT count(*) FROM %I.%I WHERE status = $1',
    schema_name,
    table_name
)
INTO matching_count
USING requested_status;

Output

The statement counts rows in the selected schema-qualified table whose status matches the bound value. The variables must be declared, the target table must exist, and the caller must have the necessary privileges.

Identifiers are formatted using %I, while the status value is passed through USING. Dynamic SQL should be limited to cases where static SQL is insufficient.

11. Input Validation Is a Complement, Not a Replacement

Parameterized queries prevent untrusted values from changing SQL syntax. Input validation serves a different purpose: it ensures that supplied values meet the application's business and data requirements.

Examples include:
- Requiring a positive quantity.
- Rejecting an empty username when the field is mandatory.
- Enforcing a maximum search length.
- Parsing an identifier as an integer.
- Limiting sort options to supported values.

A username containing punctuation is not automatically malicious. The application should apply the rules required by the username format, not reject arbitrary characters as a substitute for safe SQL construction.

Validation must be performed on the server. Client-side checks improve user experience but cannot be trusted as the only security control.

Database constraints should also enforce important data invariants, such as NOT NULL, UNIQUE, CHECK, and foreign-key constraints where appropriate.

Use parameterization for SQL safety, validation for business rules, and constraints for data integrity.

12. Why Escaping Alone Is Not Enough

Escaping attempts to make special characters safe in a particular textual context. Manual escaping is error-prone because rules differ between SQL dialects, string literal contexts, character encodings, and application layers.

A developer may escape a value incorrectly, escape it twice, or use an escaping function that is not appropriate for the active database connection.

Parameterized queries avoid the need to construct SQL string literals from untrusted values. The driver binds values using the appropriate protocol or database-specific mechanism.

Escaping may still be relevant in other contexts, such as HTML output, but HTML escaping does not prevent SQL injection. Likewise, SQL parameterization does not automatically prevent cross-site scripting or other unrelated vulnerabilities.

Choose the defense that matches the context. For SQL values, prefer parameter binding.

13. Least Privilege and Defense in Depth

Parameterized queries are the primary defense against SQL injection through bound values, but a secure application should not depend on a single control.

Use a database role that has only the permissions required by the application. A search endpoint that reads products usually does not need permission to drop tables or create database roles.

Additional controls include:
- Separate runtime and migration credentials.
- Restrict access to sensitive tables and columns.
- Apply authorization checks before returning records.
- Avoid exposing detailed SQL errors to end users.
- Keep database drivers and frameworks updated.
- Log security-relevant events without logging secrets.
- Test query construction and permission boundaries.

If an injection vulnerability exists elsewhere, a restricted database account can reduce the possible impact. It does not make the vulnerable code acceptable; it limits damage while the primary defect is fixed.

14. Safe Testing and Verification

Test SQL injection defenses only in systems you own or have explicit permission to assess.

For a local development application, use controlled test inputs that contain quotes and SQL metacharacters. Verify that they are treated as ordinary values rather than changing the query structure.

Useful checks include:
1. Confirm the application passes SQL and values separately to the driver.
2. Search the codebase for string concatenation or interpolation near SQL execution methods.
3. Test valid, missing, malformed, and boundary inputs.
4. Verify that the application returns only authorized records.
5. Confirm database errors do not expose query text, credentials, or internal schema details.
6. Review database permissions using the restricted runtime role.

A failed login or an error response alone does not prove that the application is secure. Review the query construction and test the behavior systematically.

Automated security scanners can help identify suspicious patterns, but manual code review and tests remain important.

15. Common SQL Injection Mistakes

**Mistake 1: Concatenating input into SQL.** This allows untrusted input to alter query structure.

**Mistake 2: Using string interpolation with a placeholder-looking value.** A string such as f"SELECT ... WHERE id = {user_id}" is still string construction, not parameter binding.

**Mistake 3: Treating validation as the only defense.** A value that passes a basic validation rule can still cause trouble if inserted into SQL syntax unsafely.

**Mistake 4: Assuming all placeholders are interchangeable.** PostgreSQL drivers, Python drivers, and other database libraries may use different parameter syntax.

**Mistake 5: Trying to bind table names as ordinary parameters.** Value placeholders are generally for values, not SQL identifiers.

**Mistake 6: Returning detailed database errors to clients.** Detailed messages can expose schema and implementation information. Log diagnostic details securely on the server instead.

**Mistake 7: Giving the application excessive database privileges.** Overly powerful credentials increase the impact of a vulnerability.

Correct these issues at the query-construction layer, then add validation, authorization, secure error handling, and least privilege.

16. Hands-On Practice

Complete these exercises in a local application and test database.

**Exercise 1 — Identify the vulnerability:** Review a query built using string concatenation. Explain how user input could alter the generated SQL statement.

**Exercise 2 — Fix the query:** Rewrite a username lookup using PostgreSQL parameters in Node.js with the pg package.

**Exercise 3 — Python practice:** Rewrite a product search using psycopg's parameter-binding API. Do not use an f-string to insert the search term.

**Exercise 4 — Go practice:** Use database/sql with a PostgreSQL driver to fetch a user by ID. Bind the ID as a parameter and handle sql.ErrNoRows.

**Exercise 5 — Dynamic sorting:** Build an endpoint that accepts only name or price as sort options. Map these choices to fixed identifiers and bind all filter values separately.

**Exercise 6 — Error handling:** Make a test endpoint return a generic error to the caller while recording an appropriate server-side diagnostic.

**Exercise 7 — Least privilege:** Create a test role with read-only access to a products table. Confirm that it cannot modify that table.

**Exercise 8 — Code review:** Search a sample backend for SQL-related string concatenation and interpolation. Identify each instance and determine whether it is safe.

Do not run security tests against third-party systems without authorization. Never use production customer data for these exercises.

17. Knowledge Check

**Question 1:** What is SQL injection?

A. A way to speed up every query
B. A vulnerability where untrusted input changes SQL statement structure
C. A database backup method
D. A type of index

**Answer:** B. SQL injection occurs when untrusted input is interpreted as SQL syntax.

**Question 2:** What is the main benefit of parameterized queries?

A. They remove the need for authentication
B. They automatically authorize every request
C. They separate SQL structure from supplied values
D. They eliminate all database errors

**Answer:** C. Parameterization prevents bound values from being interpreted as part of the SQL statement's grammar.

**Question 3:** Which is the safe Node.js pattern using pg?

A. Build the SQL with string concatenation
B. Pass SQL text and an array of values to pool.query
C. Put the value directly into the SQL string
D. Escape all values manually and concatenate them

**Answer:** B. The pg driver supports passing a query and its bound values separately.

**Question 4:** In psycopg, how should a value normally be passed to execute?

A. Insert it into an f-string
B. Concatenate it with the SQL
C. Pass the SQL and a parameter tuple separately
D. Put the value in a SQL comment

**Answer:** C. Pass parameters separately using the driver's supported placeholder syntax.

**Question 5:** Can a regular value placeholder generally replace a table name?

A. Yes, in every database
B. No
C. Only for SELECT *
D. Only when the table is empty

**Answer:** B. Table and column identifiers require safe identifier handling rather than ordinary value parameters.

**Question 6:** Does input validation replace parameterized queries?

A. Yes
B. No

**Answer:** B. Validation checks application rules; parameterization protects SQL query construction.

**Question 7:** What does least privilege contribute?

A. It prevents every possible vulnerability
B. It limits the database permissions available to an application
C. It removes the need for parameterized queries
D. It disables SQL

**Answer:** B. Least privilege limits the potential impact of compromised application credentials.

**Question 8:** Why should detailed SQL errors generally not be returned to users?

A. They always corrupt the database
B. They can reveal internal implementation and schema details
C. They prevent parameterization
D. They make indexes slower

**Answer:** B. Detailed database errors can expose information that helps an attacker understand the application.

18. Interview Questions and Answers

**Q1. What is SQL injection?**

SQL injection is a vulnerability where untrusted input changes the intended structure or meaning of a SQL statement because the application constructs the query unsafely.

**Q2. What causes SQL injection?**

A common cause is concatenating or interpolating untrusted values directly into executable SQL text.

**Q3. What is a parameterized query?**

It is a query that uses placeholders for values and supplies those values separately through a database driver. This prevents the values from being interpreted as SQL syntax.

**Q4. Are prepared statements and parameterized queries related?**

Yes. Parameterized execution commonly uses prepared-statement mechanisms or equivalent driver/database protocols to separate query structure from values. Exact implementation details depend on the driver and database.

**Q5. How do you prevent SQL injection in Node.js?**

With the pg package, pass SQL containing placeholders such as $1 to pool.query and provide values in a separate array.

**Q6. How do you prevent SQL injection in Python?**

Use the database driver's parameter-binding API. With psycopg, use its supported placeholder syntax and pass parameters separately to execute.

**Q7. Why is an f-string unsafe for building SQL?**

An f-string inserts the value into the SQL text before the database receives it. If the value is untrusted, it may alter SQL syntax.

**Q8. Can parameters be used for table names or column names?**

Ordinary value placeholders generally cannot represent SQL identifiers. Use fixed allowlists and safe identifier-quoting mechanisms where dynamic identifiers are necessary.

**Q9. Is input validation enough to prevent SQL injection?**

No. Validation enforces application rules, but parameterized queries are the primary defense against SQL injection through data values.

**Q10. What is the role of least privilege?**

It limits the database operations an application can perform. This can reduce the impact of a vulnerability but does not replace fixing unsafe query construction.

**Q11. Why should database errors be handled carefully?**

Detailed errors may reveal SQL text, table names, or implementation details. Applications should return appropriate generic errors while keeping necessary diagnostics in secure server logs.

**Q12. Does parameterization prevent every security vulnerability?**

No. It protects SQL construction involving bound values. Applications still need authorization, authentication, validation, secure session handling, and defenses against other vulnerability classes.

19. Lesson Summary

SQL injection is caused by unsafe construction of SQL statements using untrusted input.

Remember:
- Never concatenate untrusted values directly into SQL.
- Use parameterized queries through your database driver's API.
- Understand the placeholder syntax required by each driver.
- Use allowlists and safe identifier handling when table or column names must vary.
- Validate input according to business requirements, but do not treat validation or escaping as a replacement for parameterization.
- Use least-privilege database roles and secure error handling.
- Test your code in authorized development environments.

You should now be able to identify an unsafe SQL query, rewrite it using parameter binding, explain how Node.js, Go, and Python drivers pass values, and describe the additional controls needed for a secure database-backed application.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Concatenating untrusted input into SQL

Why it happens: String concatenation appears simple and may work correctly with ordinary inputs.

Incorrect Approach

sql

const sql =
  "SELECT user_id FROM app_users WHERE username = '" +
  username +
  "'";

Correct Solution

sql

const result = await pool.query(
  "SELECT user_id FROM app_users WHERE username = $1",
  [username]
);

Key takeaway: Pass the SQL statement and the value separately through the driver's parameter-binding API. This example uses PostgreSQL and the Node.js pg package.

#2 Using an f-string to construct a Python SQL query

Why it happens: Python f-strings are convenient for ordinary text, but they insert values into the SQL statement before execution.

Incorrect Approach

sql

cursor.execute(
    f"SELECT user_id FROM app_users WHERE username = '{username}'"
)

Correct Solution

sql

cursor.execute(
    "SELECT user_id FROM app_users WHERE username = %s",
    (username,)
)

Key takeaway: With psycopg, use its supported placeholder syntax and pass parameters separately. Other Python drivers may use different placeholder formats.

#3 Trying to bind an SQL identifier as a value

Why it happens: Developers assume placeholders can represent any part of a SQL statement.

Incorrect Approach

sql

SELECT * FROM $1;

Correct Solution

sql

-- Choose from a fixed, trusted allowlist of table names.
-- Quote identifiers with the appropriate driver facility
-- when dynamic identifiers are genuinely necessary.

Key takeaway: Ordinary query parameters represent values, not table names or column names. Validate dynamic identifiers against an allowlist and use safe identifier quoting.

#4 Treating input validation as a replacement for parameterization

Why it happens: Developers may assume that rejecting a few suspicious characters is enough to make query construction safe.

Incorrect Approach

sql

-- Validate a username and then concatenate it
-- into SQL anyway. Validation alone is not sufficient.

Correct Solution

sql

-- Validate business rules, then bind the value.
SELECT user_id
FROM app_users
WHERE username = $1;

Key takeaway: Use validation for input requirements and parameter binding for SQL safety. Do not rely on character filters to secure concatenated SQL.

#5 Using an overprivileged database account

Why it happens: A powerful account can make development setup easier and mask missing permission configuration.

Incorrect Approach

sql

-- Avoid running ordinary application queries
-- with a PostgreSQL superuser account.

Correct Solution

sql

-- Create a dedicated runtime role and grant only
-- the privileges needed by the application.
-- Use a separate controlled role for migrations.

Key takeaway: Least privilege limits the impact of application defects and compromised credentials. It complements, but does not replace, parameterized queries.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

SQL injection occurs when untrusted input is interpreted as part of SQL syntax instead of data.
String concatenation is unsafe when untrusted values are inserted directly into SQL statements.
Parameterized queries separate SQL structure from the values supplied at execution time.
Placeholders and parameter syntax vary between database drivers.
Query parameters generally represent values, not table names, column names, or SQL keywords.
Input validation and escaping do not replace parameterized queries.
Least-privilege database roles reduce the potential impact of a successful attack.

Was this lesson helpful?

Found an error? Report an issue