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.