SQL Server Dynamic SQL and SQL Injection

 SQL Server Stored Procedures: Part-4, Click here.

Dynamic SQL is used to construct SQL statement dynamically at run time. In case you don’t know all the SQL statement at compilation time, dynamic SQL is effective. You can use dynamic SQL to create Stored Procedure to query data whose table name is unknown at compilation time.

For Example:

CREATE PROC uspEmployee(
    @tableName NVARCHAR(100),
    @topN INT,
    @Column NVARCHAR(100)
    )
AS
BEGIN
    DECLARE 
        @sql NVARCHAR(MAX),
        @topNStr NVARCHAR(MAX) 
    SET @topNStr  = CAST(@topN as nvarchar(max)) 
    SET @sql = N'SELECT TOP ' +  @topNStr  + 
                ' * FROM ' + @tableName + 
                ' ORDER BY ' + @Column + ' DESC'
    EXEC sp_executesql @sql    
END

 

Syntax description:

First, give parameters to be passed while creating procedure.

CREATE PROC uspEmployee(
    @tableName NVARCHAR(100),
    @topN INT,
    @Column NVARCHAR(100)
)

Second, Declare variables @topNStr to hold table query and @sql to store dynamic SQL.

DECLARE 
    @sql NVARCHAR(MAX),
    @topNStr NVARCHAR(MAX);

Third, convert  INT type to NVARCHAR to the number of column you want.

SET @topNStr  = CAST(@topN as nvarchar(max));

 Fourth, create dynamic SQL using SELECT concatenation and store in @sql.

SET @sql = N'SELECT TOP ' +  @topNStr  + 
           ' * FROM ' + @tableName + 
           ' ORDER BY ' + @Column + ' DESC'

Lastly, execute the dynamic SQL using sp_executesql

EXEC sp_executesql @sql;

And calling uspEmployee stored procedure:

EXEC uspEmployee 'dbo.final',10,'Salary'

Output:

 

Dynamic SQL and SQL injection in SQL Server:

First, create dbo.demo table and a stored procedure uspEmployeeTb:

CREATE TABLE dbo.demo(ID INT);

CREATE PROC uspEmployeeTb(@table NVARCHAR(100))	
AS
BEGIN
  DECLARE    
    @sql NVARCHAR(MAX)
  SET @sql = N'SELECT * FROM ' + @table 
  EXEC sp_executesql @sql
END

Query to return all rows in dbo.final table:

EXEC uspEmployeeTb'dbo.final'

But, this does not prevent form SQL injection as follows:

EXEC uspEmployeeTb'dbo.final;DROP TABLE dbo.demo'

This query returned all the rows in dbo.final as well as drop the demo table which is known as SQL injection.

So, to prevent it from SQL Injection process should be as follows:

CREATE OR ALTER PROC uspEmployeeTb
    (
    @schema NVARCHAR(100), 
    @table  NVARCHAR(100)
    )
AS
    BEGIN
        DECLARE 
            @sql NVARCHAR(MAX);
        SET @sql = N'SELECT * FROM ' 
            + QUOTENAME(@schema) 
            + '.' 
            + QUOTENAME(@table);
        EXEC sp_executesql @sql
     END

Lets execute the procedure:

EXEC uspEmployeeTb 'dbo','final'

Output:

Lets try to inject another query in this stored procedure:

EXEC uspEmployeeTb 'dbo','final;DROP TABLE dbo.demo'

Output:

End of SQL Server Stored Procedures.