How string concatenation creates vulnerabilities in SQL - Visual Walkthrough

Concept Flow - How string concatenation creates vulnerabilities

User Input Received

↓

Concatenate Input into SQL Query String

↓

Query String with User Input

↓

Send Query to Database

↓

Database Executes Query

↓

If Input Malicious -> Unexpected Behavior

↓

Security Vulnerability Occurs

User input is joined directly into a SQL query string, which the database executes. If input is malicious, it can change the query meaning, causing vulnerabilities.

Execution Sample

SQL

DECLARE @userInput NVARCHAR(100) = '1; DROP TABLE Users;';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Products WHERE ProductID = ' + @userInput;
EXEC(@sql);

This code builds a SQL query by joining user input directly, then runs it. Malicious input can alter the query.

Execution Table

Step	Action	Value of @userInput	Value of @sql	Effect
1	Set user input	'1; DROP TABLE Users;'	NULL	User input contains malicious SQL
2	Concatenate input into query	'1; DROP TABLE Users;'	'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'	Query string now includes malicious command
3	Execute query	'1; DROP TABLE Users;'	'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'	Database runs both SELECT and DROP TABLE commands
4	Result	'1; DROP TABLE Users;'	'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'	Users table is dropped, vulnerability exploited

💡 Execution stops after malicious query runs, causing data loss

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
@userInput	NULL	'1; DROP TABLE Users;'	'1; DROP TABLE Users;'	'1; DROP TABLE Users;'	'1; DROP TABLE Users;'
@sql	NULL	NULL	'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'	'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'	'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'

Key Moments - 2 Insights

Why does concatenating user input directly into the SQL query cause a problem?

Because the input can contain SQL commands that change the query's meaning, as shown in step 2 and 3 of the execution table where the malicious DROP TABLE command is added and executed.

Why does the database execute both the SELECT and DROP TABLE commands?

Because the concatenated string includes both commands separated by a semicolon, so the database treats it as two commands to run, as seen in step 3.

Visual Quiz - 3 Questions

Look at the execution table, what is the value of @sql after concatenation in step 2?

A 'SELECT * FROM Products WHERE ProductID = 1;'

B 'SELECT * FROM Products WHERE ProductID = 1; DROP TABLE Users;'

C 'DROP TABLE Users;'

D 'SELECT * FROM Products WHERE ProductID = '

💡 Hint

Check the 'Value of @sql' column in step 2 of the execution table.

At which step does the database execute the malicious DROP TABLE command?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Effect' column in the execution table to see when the command runs.

If the user input was '5' instead of '1; DROP TABLE Users;', what would happen?

A The query would only select product with ID 5 safely.

B The database would drop the Users table anyway.

C The query would fail to run.

D The query would select all products.

💡 Hint

Think about how the concatenated query string changes with safe input, check variable_tracker for @userInput.

Concept Snapshot

String concatenation in SQL joins user input directly into query strings.
If input is malicious, it can add harmful commands.
This causes SQL injection vulnerabilities.
Always avoid direct concatenation; use parameterized queries instead.
This protects the database from unexpected commands.

Full Transcript

This visual execution shows how concatenating user input directly into a SQL query string creates vulnerabilities. The user input '@userInput' is set to a malicious string containing a DROP TABLE command. When concatenated into '@sql', the query string includes both a SELECT and DROP TABLE command. Executing '@sql' causes the database to run both commands, dropping the Users table. The variable tracker shows how '@userInput' and '@sql' change step-by-step. Key moments highlight why direct concatenation is dangerous and how the database executes multiple commands. The quiz tests understanding of the concatenated query and when the malicious command runs. The quick snapshot reminds to avoid string concatenation and use safe parameterized queries to prevent SQL injection.