How string concatenation creates vulnerabilities in SQL - Try It Yourself

How string concatenation creates vulnerabilities

📖 Scenario: You are building a simple user login system for a website. The system needs to check if a username and password match a record in the database.To do this, you write a SQL query that uses string concatenation to insert the username and password directly into the query.

🎯 Goal: Learn how using string concatenation to build SQL queries can create security risks like SQL injection.You will create a simple SQL query using string concatenation and see why it is dangerous.

📋 What You'll Learn

Create a SQL query string that uses string concatenation to insert username and password variables

Add a variable for the username input

Add a variable for the password input

Write the full SQL query string using concatenation of these variables

Show how the final query string looks with the variables inserted

💡 Why This Matters

🌍 Real World

Web applications often need to check user credentials against a database. Understanding how SQL injection works helps developers write safer code.

💼 Career

Database developers, backend engineers, and security professionals must know how to avoid SQL injection to protect applications and user data.

1

Create username and password variables

Create two variables called username and password and set them to the exact values 'admin' and '1234' respectively.

SQL

# Create variables username and password with exact values
# Your code here

Hint

Use simple assignment statements to create the variables.

2

Create a SQL query string using string concatenation

Create a variable called query that builds a SQL SELECT statement as a string. Use string concatenation to insert the username and password variables into the query exactly like this: "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'".

SQL

username = 'admin'
password = '1234'
# Create the query string using concatenation
# Your code here

Hint

Use the + operator to join strings and variables.

3

Explain why this string concatenation is dangerous

Add a comment line explaining that using string concatenation to build SQL queries can allow attackers to insert malicious SQL code, causing SQL injection vulnerabilities.

SQL

username = 'admin'
password = '1234'
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"
# Add a comment explaining the risk of SQL injection
# Your code here

Hint

Write a clear comment about the risk of SQL injection.

4

Show an example of a malicious input

Change the username variable to the exact string "' OR '1'='1" to simulate an attacker trying to bypass login. Then update the query variable using the same string concatenation method to show how the query becomes unsafe.

SQL

username = 'admin'
password = '1234'
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"
# This method is dangerous because attackers can inject SQL code through username or password inputs causing SQL injection
# Change username to a malicious string and update query
# Your code here

Hint

Use the exact malicious string for username and rebuild the query string.

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