HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

How does use of sp_executesql with parameters protect against SQL injection?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sql-injectionsqldbasql-serverstackoverflowdynamic-sql
withsqlagainstsp_executesqldoeshowinjectionuseparametersprotect

Problem

The following is a dynamic filtering solution that uses sp_executesql

IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders;
GO
CREATE PROC dbo.GetOrders
 @orderid AS INT = NULL,
 @custid AS INT = NULL,
 @empid AS INT = NULL,
 @orderdate AS DATE = NULL
AS
DECLARE @sql AS NVARCHAR(1000);
SET @sql = 
 N'SELECT orderid, custid, empid, orderdate, filler'
 + N' /* 27702431-107C-478C-8157-6DFCECC148DD */'
 + N' FROM dbo.Orders'
 + N' WHERE 1 = 1'
 + CASE WHEN @orderid IS NOT NULL THEN
 N' AND orderid = @oid' ELSE N'' END
 + CASE WHEN @custid IS NOT NULL THEN
 N' AND custid = @cid' ELSE N'' END
 + CASE WHEN @empid IS NOT NULL THEN
 N' AND empid = @eid' ELSE N'' END
 + CASE WHEN @orderdate IS NOT NULL THEN
 N' AND orderdate = @dt' ELSE N'' END;
EXEC sp_executesql
 @stmt = @sql,
 @params = N'@oid AS INT, @cid AS INT, @eid AS INT, @dt AS DATE',
 @oid = @orderid,
 @cid = @custid,
 @eid = @empid,
 @dt = @orderdate;
GO


On p 541 of T-SQL Querying, it says

Because the dynamic code uses parameters rather than injecting the
constants into the code, it is not exposed to SQL injection attacks.

How does the use of parameters in sp_executesql protect against SQL injection?

Thank you

Solution

double-up

To answer your question, you need to experiment with alternatives to using sp_executesql with parameters:

  • Using EXEC (without sp_executesql)



  • Using sp_executesql (without parameters)



Both of which can lead to SQL injection attacks, under the right circumstances.

It's probably worth noting that even totally unparameterized, the code above is relatively low-risk, since the data types being passed are not string types, but it's still possible.

Strings carry a much higher risk of a malicious payload.

The code examples below are from my presentation about using dynamic SQL in a different context, but apply pretty well to your question.
string-safe

You can use code like this safely, because the user input isn't part of the string that gets executed:

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @TableName sysname = N'Votes';

IF @TableName = N'Votes'
BEGIN
    SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Votes AS v;'
END

IF @TableName = N'Posts'
BEGIN
    SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p;'
END

EXEC(@SQLString);
GO


unsafe-strings

In this example, user input is concatenated into the string that gets executed, and isn't parameterized. This can cause problems:

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end? */
EXEC (@SQLString);


The end result is a query that gets executed like so, which searches the Title column for a single wildcard, and then an additional result that lists all the tables in the database.

SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656  
  AND p.Title LIKE '%' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --%' 
  ORDER BY p.Score DESC;


While many people will focus on memes like dropping tables, the real issue with dynamic SQL is usually theft of data. That's where the money is.
still-not-safe

Using sp_executesql is a good first step, but it still needs to be used with parameters. Code like below is still subject to SQL injection in the same way as above.

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --';
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;
/* Check the results -- what's that at the end? */
EXEC sys.sp_executesql 
    @SQLString;


The same query as above will be executed.
back-to-safety

Using code that lines up better with your example, we can avoid SQL injection by assigning the value to a parameter instead of concatenating it directly into the string.

```
DECLARE
@SQLString nvarchar(MAX) = N'',
@Filter nvarchar(MAX) = N'',
@Title nvarchar(250) = N'''
UNION ALL
SELECT
t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL
FROM sys.tables AS t --';
/ This ends the current statement, and adds in some sneaky code /

SET @SQLString += N'
SELECT TOP (5000)
p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656 ';

/ This appends the sneaky code onto our harmless query /
IF @Title IS NOT NULL
BEGIN
SET @Filter = @Filt

Code Snippets

DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @TableName sysname = N'Votes';

IF @TableName = N'Votes'
BEGIN
    SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Votes AS v;'
END

IF @TableName = N'Posts'
BEGIN
    SET @SQLString += N'SELECT COUNT_BIG(*) AS records FROM dbo.Posts AS p;'
END

EXEC(@SQLString);
GO
DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end? */
EXEC (@SQLString);
SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656  
  AND p.Title LIKE '%' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --%' 
  ORDER BY p.Score DESC;
DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL   
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --';
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE ''' + N'%' + @Title + N'%''';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;
/* Check the results -- what's that at the end? */
EXEC sys.sp_executesql 
    @SQLString;
DECLARE 
    @SQLString nvarchar(MAX) = N'',
    @Filter nvarchar(MAX) = N'',
    @Title nvarchar(250) = N''' 
  UNION ALL 
  SELECT 
      t.object_id, t.schema_id, t.name, SCHEMA_NAME(t.schema_id), t.create_date, t.modify_date, NULL 
  FROM sys.tables AS t --'; 
/* This ends the current statement, and adds in some sneaky code */

SET @SQLString += N' 
  SELECT TOP (5000) 
      p.OwnerUserId, p.Score, p.Tags, p.Title, p.CreationDate, p.LastActivityDate, p.Body 
  FROM dbo.Posts AS p 
  WHERE p.OwnerUserId = 22656 ';

/* This appends the sneaky code onto our harmless query */
IF @Title IS NOT NULL
BEGIN
    SET @Filter = @Filter + N' 
  AND p.Title LIKE N''%'' + @Title + N''%'' ';
END;

IF @Filter IS NOT NULL
BEGIN
    SET @SQLString += @Filter;
END;

SET @SQLString += N' 
  ORDER BY p.Score DESC;';

/* Check the messages tab... */
RAISERROR('%s', 0, 1, @SQLString) WITH NOWAIT;

/* Check the results -- what's that at the end now? */
EXEC sys.sp_executesql 
    @SQLString, 
  N'@Title NVARCHAR(250)', 
    @Title;

Context

StackExchange Database Administrators Q#305558, answer score: 13

Revisions (0)

No revisions yet.