Second-Order SQL Injection: When Stored Data Becomes Code

Second-order SQL injection is bad text that waits in your tables and does its damage later. You parameterized the insert, so the data went in clean. The trouble starts when another query reads that value back and pastes it into a new statement.

Why a safe insert is not the end of the story

A junior DBA once asked me, “We use parameters everywhere. How did the nightly report return every customer?” Fair question. The answer was sitting in a saved-search table.

The insert was fine. The value went in as plain text. Weeks later a report job read that text and glued it into a query string. At that moment the text became code.

That is the “second” in second-order. The first order is input hitting a query directly. The second order is input that was stored and trusted on the way back out. Let me show it on a few temp tables, so you can paste it into any query window.

Store the nasty text safely

First, three customers and a table for saved searches. Everything is a temp table, so nothing is left behind when your session ends.

DROP TABLE IF EXISTS #SavedSearch, #Customers;

CREATE TABLE #Customers (CustomerId int PRIMARY KEY, CustomerName nvarchar(100), City nvarchar(50));
INSERT #Customers (CustomerId, CustomerName, City)
VALUES (1, N'Ava Stone', N'Austin'),
       (2, N'Ben Okafor', N'Boston'),
       (3, N'Cara Lin', N'Chicago');

CREATE TABLE #SavedSearch (UserId int PRIMARY KEY, SearchText nvarchar(100));

Now a user saves a search term that looks like a trick. The insert uses a parameter, so it is perfectly safe.

DECLARE @Typed nvarchar(100) = N'x'' OR ''1''=''1';

EXEC sys.sp_executesql
    N'INSERT #SavedSearch (UserId, SearchText) VALUES (1, @Text);',
    N'@Text nvarchar(100)',
    @Text = @Typed;

SELECT UserId, SearchText FROM #SavedSearch;

The result shows the text exactly as typed: x’ OR ‘1’=’1. Nothing ran. It is just a string in a column. If you only test the insert, you will call this secure and go home.

The report that trusts the table

Now the nightly report. It reads the saved text and builds its query by concatenation. I print the built query first, so you can see what SQL Server will receive.

DECLARE @Saved nvarchar(100) = (SELECT SearchText FROM #SavedSearch WHERE UserId = 1);

DECLARE @Sql nvarchar(max) =
    N'SELECT CustomerId, CustomerName FROM #Customers WHERE CustomerName = '''
    + @Saved + N''' ORDER BY CustomerId;';

SELECT @Sql AS BuiltQuery;

EXEC (@Sql);

Read the built query. The saved quote mark closed the string early, and the rest became a new condition: OR ‘1’=’1′. That is always true. So the report returns all three customers, not zero.

My sample text only widens a filter. A real attacker would try something far nastier. The mechanism is the same.

Bind the value again at the final consumer

The fix is boring, which is why it works. Every query that uses a stored value gets its own parameter, even if the value came from your own table.

DECLARE @Saved nvarchar(100) = (SELECT SearchText FROM #SavedSearch WHERE UserId = 1);

EXEC sys.sp_executesql
    N'SELECT CustomerId, CustomerName FROM #Customers WHERE CustomerName = @Name ORDER BY CustomerId;',
    N'@Name nvarchar(100)',
    @Name = @Saved;

SET @Saved = N'Ava Stone';

EXEC sys.sp_executesql
    N'SELECT CustomerId, CustomerName FROM #Customers WHERE CustomerName = @Name ORDER BY CustomerId;',
    N'@Name nvarchar(100)',
    @Name = @Saved;

The first call returns no rows. The tricky text is searched for as a name, and no customer has that name. The second call finds Ava Stone, so real searches still work. Do not “clean” quotes when you store the data. Someone named O’Brien deserves to be found, and cleaned data only hides the real problem.

Names of columns need a different tool

A parameter can carry a value. It cannot carry a column name. So a saved “sort by” setting is a different puzzle, and people often fall back to concatenation. Use an approved list instead. Anything not on the list is rejected, and anything on the list is wrapped with QUOTENAME.

DECLARE @Allowed TABLE (ColumnName sysname PRIMARY KEY);
INSERT @Allowed (ColumnName) VALUES (N'CustomerName'), (N'City');

SELECT p.SavedPick,
       CASE WHEN a.ColumnName IS NULL THEN N'rejected'
            ELSE N'ORDER BY ' + QUOTENAME(a.ColumnName) END AS Decision
FROM (VALUES (N'City'), (N'CustomerName; DROP TABLE #Customers')) AS p (SavedPick)
LEFT JOIN @Allowed AS a ON a.ColumnName = p.SavedPick
ORDER BY p.SavedPick;

City is approved. The second saved pick, with the extra command stuck to it, is rejected. Only the approved name reaches the final query.

DECLARE @Pick sysname = N'City';
DECLARE @Sql nvarchar(max) =
    N'SELECT CustomerId, CustomerName, City FROM #Customers ORDER BY '
    + QUOTENAME(@Pick) + N' DESC, CustomerId;';

EXEC (@Sql);

The customers come back sorted by city from Z to A, so Chicago comes first. Test your own code with both a permitted name and a rejected one, under the same login the job really uses.

Hunt for it on your own server

Search your procedures for dynamic SQL. This query lists every module that calls EXEC with a string. In my empty demo it returns nothing. On your server, expect a list.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS ObjectName
FROM sys.sql_modules
WHERE definition LIKE N'%EXEC (%' OR definition LIKE N'%EXEC(%'
ORDER BY SchemaName, ObjectName;

For each hit, ask one question: where does this text come from? If the answer is a table, a file, or an old import, treat it like fresh user input. Then clean up the demo.

DROP TABLE IF EXISTS #SavedSearch, #Customers;

Next time a report reads a value back from a table, bind it again, even if you wrote it yourself.

Stored input is not trusted syntax, it is input at every later use.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.