ttmind

Main Navigation

ttmind
  • jim-jams
  • Tech
  • Positive
Login

Login

Facebook Google

OR

Remember me Forgot password?

Don't have account? Signup here.

Sort by Categorys

.Net

PHP

Java

JavaScript

Database

Server

Client Side

Tools

Artificial Intelligence

Cloud

Hybrid Development

Event

Smart City

Education

Security

Scrum

Digital Marketing

APP Development

Business

Internet

Simulation

Art

Network

Microservices

Architecture

Technology

Leadership

    Top Articles

  • How Does Social Media Bring People Together?
    TTMind Author
  • How to read appSettings JSON from Class Library in ASP.NET Core
    Anil Shrestha
  • Printing Support In Asp.Net Core
    TTMind Author
  • HOW TO EXTRACT TEXT FROM IMAGE USING JAVASCRIPT (OCR with Tesseract.js)?
    Prakash Pokhrel
  • Images Upload REST API using ASP.NET Core
    Prakash Pokhrel
  • Related Topic

  • Hosting ASP.NET Core Web API on IIS
  • Use of TOP and DISTINCT in SQL SERVER
  • Difference between char, nchar, varchar, and nvarchar in MS SQL Server
  • What's new in SQL Server 2019?
  • Uploading .csv sample table file data to SQL Server
  • Tech
  • About Us
  • Contact Us
  • TechHelp
  • PositiveHelp
  • Jim-Jams Help
  • Terms & Conditions

© Copyright ttmind.com

Main Content

SQL Server Stored Procedures: Part-5

Server SQL Server about 6 years ago || 6/7/2019 || 2.9 K View

  • Hide

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.

  • 0
  • 0
  • 0
    • Facebook
    • Twitter
    • Google +
    • LinkedIn

About author

MAT RIX

MAT RIX

Reset Your Password
Enter your email address that you used to register. We'll send you an email with your username and a link to reset your password.

Quick Survey