How string concatenation creates vulnerabilities in SQL

Introduction

String concatenation in SQL can cause security problems because it lets attackers change the commands sent to the database.

When building SQL queries by joining user input with SQL code.

When creating dynamic search filters based on user choices.

When generating reports that use user-supplied data in queries.

When updating or deleting records based on user input.

When logging or auditing actions that include user data.

Syntax

SQL

SELECT 'Hello, ' || 'world!' AS greeting;

|| is the standard SQL operator for joining strings.

Using + for string concatenation depends on the database system.

Examples

Joins the text 'User: ' with the username from each row.

SQL

SELECT 'User: ' || username FROM users;

Combines multiple columns with labels into one string.

SQL

SELECT 'ID=' || id || ' Name=' || name FROM customers;

Builds a SQL query string by joining user input directly (this is risky).

SQL

SELECT 'SELECT * FROM users WHERE name = ''' || user_input || ''';' AS query;

Sample Program

This example shows how joining user input directly into a query string can create a dangerous command that always returns true.

SQL

CREATE TABLE users (id INT, name VARCHAR(50));
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');

-- Unsafe query using string concatenation
-- Imagine user_input = "Bob' OR '1'='1"

SELECT 'SELECT * FROM users WHERE name = ''' || 'Bob'' OR ''1''=''1' || ''';' AS unsafe_query;

Important Notes

Never trust user input directly in SQL commands.

Use parameterized queries or prepared statements to avoid injection risks.

String concatenation can make queries easy to hack if not handled carefully.

Summary

String concatenation joins pieces of text or data in SQL.

Using it with user input can let attackers change your queries.

Always use safe methods like parameters to protect your database.