{"id":166,"date":"2019-09-24T08:01:17","date_gmt":"2019-09-24T08:01:17","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=166"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"dynamic-sql","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/dynamic-sql\/","title":{"rendered":"Dynamic SQL"},"content":{"rendered":"\n

Introduction to Dynamic SQL<\/h2>\n\n\n\n

Dynamic SQL is a programming technique that allows you to construct SQL statements dynamically at runtime. It allows you to create more general purpose and flexible SQL statement because the full text of the SQL statements may be unknown at compilation. For example, you can use the dynamic SQL to create a stored procedure<\/a> that queries data against a table whose name is not known until runtime.<\/p>\n\n\n\n

Creating a dynamic SQL is simple, you just need to make it a string as follows:<\/p>\n\n\n\n
1<\/td>‘SELECT * FROM production.products’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

To execute a dynamic SQL statement, you call the stored procedure sp_executesql<\/code> as shown in the following statement:<\/p>\n\n\n\n
1<\/td>EXEC sp_executesql N’SELECT * FROM production.products’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Because the sp_executesql<\/code> accepts the dynamic SQL as a Unicode string, you need to prefix it with an N<\/code>.<\/p>\n\n\n\n

Though this dynamic SQL is not very useful, it illustrates a dynamic SQL very well.<\/p>\n\n\n\n

Using dynamic SQL to query from any table example<\/h3>\n\n\n\n

First, declare two variables, @table<\/code> for holding the name of the table from which you want to query and @sql<\/code> for holding the dynamic SQL.<\/p>\n\n\n\n
123<\/td>DECLARE     @table NVARCHAR(128),    @sql NVARCHAR(MAX);<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Second, set the value of the @table<\/code> variable to production.products<\/code>.<\/p>\n\n\n\n
1<\/td>SET @table = N’production.products’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Third, construct the dynamic SQL by concatenating the SELECT<\/code> statement with the table name parameter:<\/p>\n\n\n\n
1<\/td>SET @sql = N’SELECT * FROM ‘ + @table;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Fourth, call the sp_executesql<\/code> stored procedure by passing the @sql<\/code> parameter.<\/p>\n\n\n\n
1<\/td>EXEC sp_executesql @sql;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Putting it all together:<\/p>\n\n\n\n
123456789<\/td>DECLARE     @table NVARCHAR(128),    @sql NVARCHAR(MAX); SET @table = N’production.products’; SET @sql = N’SELECT * FROM ‘ + @table; EXEC sp_executesql @sql;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The code block above produces the exact result set as the following statement:<\/p>\n\n\n\n
1<\/td>SELECT * FROM production.products;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

To query data from another table, you change the value of the @table<\/code> variable. However, it\u2019s more practical if we wrap the above T-SQL block in a stored procedure.<\/p>\n\n\n\n

SQL Server dynamic SQL and stored procedures<\/h3>\n\n\n\n

This stored procedure accepts any table and returns the result set from a specified table by using the dynamic SQL:<\/p>\n\n\n\n
12345678910111213<\/td>CREATE PROC usp_query (    @table NVARCHAR(128))ASBEGIN     DECLARE @sql NVARCHAR(MAX);    — construct SQL<\/em>    SET @sql = N’SELECT * FROM ‘ + @table;    — execute the SQL<\/em>    EXEC sp_executesql @sql;    END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The following statement calls the usp_query<\/code> stored procedure to return all rows from the production.brands<\/code> table:<\/p>\n\n\n\n
1<\/td>EXEC usp_query ‘production.brands’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

This stored procedure returns the top 10 rows from a table by the values of a specified column:<\/p>\n\n\n\n
123456789101112131415161718192021<\/td>CREATE OR ALTER PROC usp_query_topn(    @table NVARCHAR(128),    @topN INT,    @byColumn NVARCHAR(128))ASBEGIN    DECLARE         @sql NVARCHAR(MAX),        @topNStr NVARCHAR(MAX);     SET @topNStr  = CAST(@topN as nvarchar(max));     — construct SQL<\/em>    SET @sql = N’SELECT TOP ‘ +  @topNStr  +                 ‘ * FROM ‘ + @table +                     ‘ ORDER BY ‘ + @byColumn + ‘ DESC’;    — execute the SQL<\/em>    EXEC sp_executesql @sql;    END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

For example, you can get the top 10 most expensive products from the production.products<\/code> table:<\/p>\n\n\n\n
1234<\/td>EXEC usp_query_topn         ‘production.products’,        10,         ‘list_price’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

This statement returns the top 10 products with the highest quantity in stock:<\/p>\n\n\n\n
1234<\/td>EXEC usp_query_topn         ‘production.tocks’,        10,         ‘quantity’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

SQL Server Dynamic SQL and SQL Injection<\/h2>\n\n\n\n

Let\u2019s create a new table<\/a> named sales.tests<\/code> for the demonstration:<\/p>\n\n\n\n
1<\/td>CREATE TABLE sales.tests(id INT); <\/td><\/tr><\/tbody><\/table>\n\n\n\n

This statement returns all rows from the production.brands<\/code> table:<\/p>\n\n\n\n
1<\/td>EXEC usp_query ‘production.brands’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

But it does not prevent users from passing the table name as follows:<\/p>\n\n\n\n
1<\/td>EXEC usp_query ‘production.brands;DROP TABLE sales.tests’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

This technique is called SQL injection. Once the statement is executed, the sales.tests<\/code> table is dropped, because the stored procedure usp_query<\/code> executes both statements:<\/p>\n\n\n\n
1<\/td>SELECT * FROM production.brands;DROP TABLE sales.tests<\/td><\/tr><\/tbody><\/table>\n\n\n\n

To prevent this SQL injection, you can use the QUOTENAME()<\/a><\/code> function as shown in the following query:<\/p>\n\n\n\n
1234567891011121314151617<\/td>CREATE OR ALTER PROC usp_query(    @schema NVARCHAR(128),     @table  NVARCHAR(128))AS    BEGIN        DECLARE             @sql NVARCHAR(MAX);        — construct SQL<\/em>        SET @sql = N’SELECT * FROM ‘             + QUOTENAME(@schema)             + ‘.’             + QUOTENAME(@table);        — execute the SQL<\/em>        EXEC sp_executesql @sql;    END;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Now, if you pass the schema and table name to the stored procedure, it will work:<\/p>\n\n\n\n
1<\/td>EXEC usp_query ‘production’,’brands’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

However, if you try to inject another statement such as:<\/p>\n\n\n\n
123<\/td>EXEC usp_query         ‘production’,        ‘brands;DROP TABLE sales.tests’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

It will issue the following error:<\/p>\n\n\n\n
1<\/td>Invalid object name ‘production.brands;DROP TABLE sales.tests’.<\/td><\/tr><\/tbody><\/table>\n\n\n\n

More on sp_executesql<\/code> stored procedure<\/h2>\n\n\n\n

The sp_executesql<\/code> has the following syntax:<\/p>\n\n\n\n
123456<\/td>EXEC sp_executesql     sql_statement      parameter_definition    @param1 = value1,    @param2 = value2,    …<\/td><\/tr><\/tbody><\/table>\n\n\n\n

In this syntax:<\/p>\n\n\n\n

  • sql_statement<\/code> is a Unicode string that contains a T-SQL statement. The sql_statement<\/code> can contain parameters such as SELECT * FROM table_name WHERE id=@id<\/code><\/li>
  • parameter_definition<\/code> is a string that contains the definition of all parameters embedded in the sql_statement<\/code>. Each parameter definition consists of a parameter name and its data type e.g., @id INT<\/code>. The parameter definitions are separated by a comma (,).<\/li>
  • @param1 = value1<\/code>, @param2 = value2<\/code>,\u2026 specify a value for every parameter defined in the parameter_definition<\/code> string.<\/li><\/ul>\n\n\n\n

    This example uses the sp_executesql<\/code> stored procedure to find products which have list price greater than 100 and category 1:<\/p>\n\n\n\n
    12345678910111213<\/td>EXEC sp_executesqlN’SELECT *    FROM         production.products     WHERE         list_price> @listPrice AND        category_id = @categoryId    ORDER BY        list_price DESC’, N’@listPrice DECIMAL(10,2),@categoryId INT’,@listPrice = 100,@categoryId = 1;<\/td><\/tr><\/tbody><\/table>\n","protected":false},"excerpt":{"rendered":"

    Introduction to Dynamic SQL Dynamic SQL is a programming technique that allows you to construct SQL statements dynamically at runtime. It allows you to create more general purpose and flexible SQL statement because the full… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,18,19],"tags":[],"_links":{"self":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/166"}],"collection":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/comments?post=166"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/166\/revisions"}],"predecessor-version":[{"id":167,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/166\/revisions\/167"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=166"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}