How string concatenation creates vulnerabilities in SQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

SQL Injection Risk from String Concatenation

Consider the following SQL query built using string concatenation in an application:

sql = "SELECT * FROM users WHERE username = '" + user_input + "';"

If user_input is admin'; DROP TABLE users; --, what will be the result of executing this query?

SQL

SELECT * FROM users WHERE username = 'admin'; DROP TABLE users; --

A The query ignores the DROP TABLE command and only returns data for 'admin'.

B The query returns no data and causes a syntax error.

C The query safely returns data for the username including the special characters.

D The query returns user data for 'admin' and then deletes the entire users table.

💡 Hint

Think about how the input breaks out of the string and adds a new command.

Because the input closes the string early and adds a DROP TABLE command, the database executes both the SELECT and the DROP commands, causing data loss.

🧠 Conceptual

intermediate

Why String Concatenation is Unsafe for SQL Queries

Why is building SQL queries by concatenating strings with user input considered unsafe?

A Because concatenated strings use more memory than parameterized queries.

B Because it can allow attackers to inject malicious SQL code that the database will execute.

C Because string concatenation is slower than using prepared statements.

D Because concatenation causes syntax errors in SQL statements.

💡 Hint

Focus on security risks related to user input.

Concatenating user input directly into SQL allows attackers to add harmful commands, leading to SQL injection vulnerabilities.

📝 Syntax

advanced

Identify the Vulnerable SQL Query Syntax

Which of the following SQL query constructions is vulnerable to SQL injection due to string concatenation?

A sql = "SELECT * FROM products WHERE id = " + product_id;

B sql = "SELECT * FROM products WHERE id = ?"; // using parameter binding

C sql = "SELECT * FROM products WHERE id = $1"; // using prepared statement

D sql = "SELECT * FROM products WHERE id = :id"; // named parameter

💡 Hint

Look for direct concatenation of variables into the query string.

Option A directly concatenates product_id into the query string, which can be manipulated by attackers. The other options use safe parameter binding.

🔧 Debug

advanced

Debugging a String Concatenation Vulnerability

A developer wrote this code to get user info:

query = "SELECT * FROM users WHERE email = '" + email + "'";

Users report that entering an email with a single quote causes errors. What is the main problem?

A The email variable is not converted to lowercase.

B The query is missing a semicolon at the end.

C The single quote in the email breaks the string, causing a syntax error.

D The query uses double quotes instead of single quotes.

💡 Hint

Think about how quotes inside strings affect SQL syntax.

When the email contains a single quote, it prematurely ends the string in the SQL query, causing syntax errors or injection risks.

❓ optimization

expert

Best Practice to Prevent Injection from String Concatenation

Given the risk of SQL injection from string concatenation, which approach best prevents this vulnerability while querying a database?

A Use parameterized queries or prepared statements to separate code from data.

B Escape all single quotes in user input before concatenation.

C Limit user input length to prevent injection.

D Use string concatenation but validate input with regex.

💡 Hint

Think about how to separate user data from SQL commands safely.

Parameterized queries ensure user input is treated as data only, preventing it from being executed as SQL code.

Practice

1. What is the main security risk when using string concatenation to build SQL queries?

easy

A. It can allow attackers to inject malicious SQL code.

B. It makes queries run faster than parameterized queries.

C. It automatically encrypts user input.

D. It prevents any syntax errors in the query.

Solution

1. Step 1: Understand string concatenation in SQL queries

   Concatenating user input directly into SQL commands can mix code and data.

2. Step 2: Recognize the risk of SQL injection

   This mixing allows attackers to add harmful SQL commands, called SQL injection.

3. Final Answer:

   It can allow attackers to inject malicious SQL code. -> Option A

4. Quick Check:

   SQL injection risk = A [OK]

Hint: Concatenating user input risks injection attacks [OK]

Common Mistakes:

• Thinking concatenation speeds up queries
• Believing concatenation encrypts input
• Assuming concatenation prevents errors

2. Which of the following SQL query constructions using string concatenation is syntactically correct?

easy

A. SELECT * FROM users WHERE name = '\'' + userName + '\'';

B. SELECT * FROM users WHERE name = '\'' + userName + '\'

C. SELECT * FROM users WHERE name = ' + userName + "';

D. SELECT * FROM users WHERE name = '\'' + userName + "';

Solution

1. Step 1: Check string delimiters and concatenation syntax

   In SQL, string literals are enclosed in single quotes. Concatenation in code requires proper quotes and plus signs.

2. Step 2: Identify correct syntax for concatenation in code

   SELECT * FROM users WHERE name = '\'' + userName + '\''; correctly escapes single quotes for SQL string literals and concatenates userName with plus signs.

3. Final Answer:

   "SELECT * FROM users WHERE name = '\'' + userName + '\'';" -> Option A

4. Quick Check:

   Proper quotes and concatenation = A [OK]

Hint: Look for matching quotes and plus signs [OK]

Common Mistakes:

• Missing semicolon at end
• Mismatched or missing quotes
• Using wrong concatenation operators

3. Given the query built by concatenation: "SELECT * FROM users WHERE username = '" + input + "';" and input = admin' OR '1'='1, what will be the effect when executed?

medium

A. Returns only the user with username 'admin'.

B. Returns all users because the WHERE clause is always true.

C. Causes a syntax error and query fails.

D. Returns no rows because input is invalid.

Solution

1. Step 1: Substitute input into the query string

   The query becomes: SELECT * FROM users WHERE username = 'admin' OR '1'='1';

2. Step 2: Analyze the WHERE clause logic

   The condition '1'='1' is always true, so the WHERE clause matches all rows.

3. Final Answer:

   Returns all users because the WHERE clause is always true. -> Option B

4. Quick Check:

   Injected OR '1'='1' returns all rows = D [OK]

Hint: Injected OR '1'='1' makes WHERE always true [OK]

Common Mistakes:

• Thinking it returns only 'admin'
• Assuming syntax error occurs
• Believing no rows are returned

4. Identify the error in this SQL query construction using string concatenation: query = "SELECT * FROM users WHERE id = " + userId; where userId is a string input.

medium

A. Using plus sign for concatenation is invalid in SQL.

B. No error; query is correct.

C. Query is safe from SQL injection.

D. Missing quotes around userId value causing syntax error.

Solution

1. Step 1: Check how userId is inserted in query

   The query concatenates userId without quotes, so if userId is a string, SQL syntax breaks.

2. Step 2: Understand SQL string literal requirements

   String values must be enclosed in single quotes in SQL to be valid.

3. Final Answer:

   Missing quotes around userId value causing syntax error. -> Option D

4. Quick Check:

   String values need quotes = B [OK]

Hint: String inputs need quotes in SQL queries [OK]

Common Mistakes:

• Thinking plus sign is invalid in code
• Assuming query is safe from injection
• Ignoring missing quotes causes errors

5. You want to prevent SQL injection when building queries with user input. Which approach best avoids vulnerabilities caused by string concatenation?

hard

A. Remove all spaces from user input before concatenation.

B. Escape all single quotes in user input manually before concatenation.

C. Use parameterized queries or prepared statements instead of concatenation.

D. Convert user input to uppercase before concatenation.

Solution

1. Step 1: Understand the problem with string concatenation

   Concatenation mixes code and data, allowing injection if input is malicious.

2. Step 2: Identify secure alternatives

   Parameterized queries separate code from data, preventing injection regardless of input.

3. Final Answer:

   Use parameterized queries or prepared statements instead of concatenation. -> Option C

4. Quick Check:

   Parameterized queries prevent injection = C [OK]

Hint: Always prefer parameterized queries over concatenation [OK]

Common Mistakes:

• Relying on manual escaping which is error-prone
• Thinking removing spaces prevents injection
• Assuming uppercase conversion secures input