SQL Interview Questions and Answers - Part 23
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 23rd part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 23
Q211. What is Dynamic SQL Query(D-SQL) in SQL? How it is different from Static SQL Query?
Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. Dynamic SQL is when you write SQL code into a string variable and then execute the contents of that variable. This can be done in client code or in a stored procedure but not in UDF.
When we use Static SQL it is not altered from one execution to another, but in the case of dynamic SQL, we can alter the query in each execution. If a query compiles successfully it implies that the syntax is correct and all the permissions and validations are correct. The execution plan is generated and cached for the query
For Dynamic SQL, we use the EXECUTE command or sp_executesql for running the DSQL.
DECLARE @dynamicSQL VARCHAR(4000) = 'SELECT * FROM [dbo].[User]';
EXEC (@dynamicSQL);
Dynamic SQL Query Fundamental Points:
Always VARCHAR(MAX) or NVARCHAR(MAX) datatype variable should be used for constructing the dynamic SQL query because if you use a limited size like varchar(1000), there is the risk that the batch you compose exceeds the length you specify.
You should always sanitize your input parameter for NULL values. NULL values must be handled before string concatenation otherwise the result will be NULL unless the CONCAT_NULL_YIELDS_NULL option is set to OFF. Please watch here for more details.
When executing a dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems
When you run dynamic SQL, it runs in its own scope. Multiple D-SQLs have no interlink connection. When you run dynamic SQL, it runs in its own scope. Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
Disadvantages of Dynamic SQL Query:
- Dynamic SQL can be open to SQL injection attacks if not used properly.
- Dynamic SQL code can be tricky to debug and test
- Poorly written dynamic SQL queries can hamper the performance
Q212. How can you run the Dynamic SQL Query? What is the difference between EXECUTE(), and sp_executesql() in SQL?
For executing the Dynamic SQL Query we can use either EXECUTE() or sp_executesql stored procedure. But the following is some main difference between them:
- sp_executesql reuses the cached plan forcefully while EXEC() generates multiple plans when executed with different parameters.
- sp_executesql supports parameterization while EXEC() does not support parameterization.
- sp_executesql supports the output variable while the output variable is not supported by EXEC().
- sp_executesql is less prone to SQL Injection while EXEC() is more prone to SQL injection.
EXECUTE or EXEC:
The EXECUTE or EXEC statement executes a stored procedure by following the command with the name of the stored procedure. Please note that EXEC is not the same as EXEC().
EXECUTE procGetUserDetails;
EXECUTE() or EXEC() Function :
It is known as Dynamic String Execution (DSE) because it is used to construct the dynamic SQL query directly. EXECUTE commands can work on both Unicode and Non-Unicode data types. EXEC command does not re-use the compiled plan stored in the plan cache.
It is a system-defined SQL function that is used to build and run dynamic queries. You always need to enclose the string in the brackets otherwise EXECUTE statement considers it as a stored procedure and throws an error.
EXEC ('SELECT * FROM User');
The EXECUTE(character_string) function is not compiled until run time and the generated plan is not cached and reused. To execute a string, construct the string and pass it to the EXEC SQL command.
DECLARE @dynamicSQL NVARCHAR(MAX) = 'SELECT * FROM User';
EXEC (@dynamicSQL);
With EXEC() you can impersonate a user or log in when your run your SQL batch, which you cannot do with the sp_executesql procedure option.
EXEC ('SELECT * FROM [guest].[SalesDetails]') AS USER = 'TechUser';
The EXEC() does not support parameters (Except Linked Server), so all values have to be inlined.
sp_executesql Stored Procedure:
It is a system-stored procedure introduced in MS SQL 7.0 that accepts only UNICODE inputs (NVARCHAR). The advantage of using this procedure is that sp_executesql reuses the compiled plan forcefully when the statement is executed for different parameters.
If your D-SQL query string exceeds 4000 characters in SQL 7.0 or SQL 2000, you can not use sp_executesql, since NVARCHAR(MAX) is not available on these versions. Only EXEC() permits you to work around this because it accepts that the resulting string exceeds 8000 bytes.
DECLARE @SQL NVARCHAR(1000)
SELECT @SQL = 'SELECT * FROM [dbo].[User] WHERE Id = 1';
EXEC sp_executesql @stmt = @SQL;
The sp_executesql has strongly typed variables/parameters and this can reduce injection and offer some performance benefits. The SQL query preparing dynamic SQL string must be Unicode for sp_executesql.
sp_executesql @stmt, N'@parameter_name data_type' , @param1 = 'value1'
The sp_executesql has two fixed parameters: @stmt and @params
- @stmt is the text of the SQL code to execute and it is a mandatory parameter. But it should always be of Unicode data types (nvarchar, nchar) only.
- @params holds the parameter list for the code in @stmt. It is not mandatory but whenever is passed must be Unicode data type.
You are free to mix Unicode and Non-Unicode data types but the restriction is that you must pass nvarchar only to the fixed parameters @stmt and @params.
EXEC sp_executesql N'SELECT * FROM [dbo].[User] WHERE Id = 1';
The sp_executesql can be used when you want to execute the code in another database, rather than the one it was called from.
DECLARE @DynamicSQL NVARCHAR(1000);
Select @DynamicSQL = 'CREATE VIEW viewUserDetails AS (SELECT * FROM [TechPoint].[User])';
Execute [TechPointFunda].dbo.sp_executesql @DynamicSQL;
Q213. Can you use Dynamic SQL Query in Functions? Why?
A user-defined function cannot use a dynamic SQL query. There are two reasons for that:
A function in SQL Server is not permitted to change the database state, and obviously, SQL Server cannot check beforehand what your dynamic SQL is going to do.
The second reason is that you cannot call stored procedures in user-defined functions and a batch of dynamic SQL is a nameless stored procedure. We know that for executing the dynamic SQL we need to use EXEC() or sp_executesql procedure.
Q214. Can you use the OUT parameter in a Dynamic SQL Query?
OUTPUT Parameter in D-SQL:
The sp_executesql is an extended stored procedure that supports the output parameter to store the value returned by the select query and use the output variable in another statement.
DECLARE @Name varchar(50);
EXECUTE sp_executesql
N'SELECT @outputName = Name FROM [dbo].[User] WHERE Email = @input',
N'@input varchar(50), @outputName nvarchar(50) OUTPUT',
@input = 'thomas@gmail.com',
@outputName = @Name OUTPUT;
SELECT @Name AS UserName;
But EXEC() does not allow any OUT parameter. However, you can use it with some limitations. In difference to sp_executesql, the parameters do not have names, but instead, the question mark serves as a parameter holder. SQL Server will pass the parameters given to EXEC() in the order the question marks appear.
DECLARE @Name varchar(50)
DECLARE @input varchar(50) = 'thomas@gmail.com';
DECLARE @SQL VARCHAR (1000) = 'SELECT ? = Name FROM [TechPoint].[dbo].[User] WHERE Email = ?';
PRINT @SQL
EXEC(@SQL, @Name OUTPUT, @input) AT TechPointLinkedServer;
SELECT @Name AS UserName;
Q215. Can you return a value from the Dynamic SQL Query?
RETURN Statement in D-SQL:
A RETURN statement with a specific return value in the dynamic SQL is not allowed like C# Constructor and unlike Stored Procedure.
EXEC sp_executesql N'IF @num > 0 RETURN Positive', N'@num int', 5;
Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
But a RETURN statement with no value in the dynamic SQL is allowed like C# Constructor. You can still use RETURN without a return value in dynamic SQL. This is legal:
EXEC sp_executesql N'IF @num > 0 RETURN', N'@num int', 5;
Q216. Can you use Temp Table in Dynamic SQL Query?
Temp tables can be used to interact between batches of standard SQL and dynamic SQL.
The local temp table created by executing dynamic SQL cannot be accessed outside the execution of dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes.
EXECUTE sp_executesql N'SELECT * INTO #tempTable FROM [dbo].[User]';
SELECT * FROM #tempTable; -- ERROR :Invalid object name '#tempTable'.
However, a temp table created before the batch will be available to it. A workaround for this is to create the local temp table outside and use it in the dynamic SQL.
SELECT * INTO #tempTable FROM [dbo].[User] WHERE 1 = 2;
EXECUTE sp_executesql N'INSERT INTO #tempTable SELECT Name, Email, MobileNumber FROM [dbo].[User]';
SELECT * FROM #tempTable;
Please watch the complete Temp Table video here.
Q217. Can you use Transaction in Dynamic SQL Query?
You can use TCL in the dynamic SQL query, but it must be used within the dynamic SQL batch.
EXEC ('BEGIN TRAN DELETE FROM UserLog WHERE UserId = 2; ROLLBACK');
Q218. What is the QUOTENAME() function in SQL?
Whenever you build dynamic SQL statements where a variable holds the name of something, you should use the QUOTENAME function.
QUOTENAME returns its input surrounded by square brackets, and the right brackets are doubled (any closing delimiter).
SELECT QUOTENAME('User') -- Return : [User]
SELECT QUOTENAME('[User]') -- Returns : [[User]]]
QUOTENAME accepts a second parameter as well that permits you to specify an alternative delimiter. And any closing delimiter is always doubled.
SELECT QUOTENAME('Tech Point', '"') -- Returns : "Tech Point"
SELECT QUOTENAME('Tech Point', ']') -- Returns : [Tech Point]
SELECT QUOTENAME('[Tech Point]', ']') -- Returns : [[Tech Point]]]
Please keep in mind that QUOTENAME's input is limited to 128 characters only. If it exceeds returns NULL.
DECLARE @objectName nvarchar(255) = replicate('A', 129)
SELECT QUOTENAME(@objectName, '''') -- Returns : NULL
Q219. What is SQL Injection? How can you prevent SQL Injection attacks?
SQL Injection attacks are carried out by passing specially-formatted strings as input to the TSQL Query. SQL injection is when a user enters SQL syntax in an input field or similar and that makes the application to perform something else than intended.
In a successful attack, those special strings are passed along to a database to either execute arbitrary code or cause the server to return unanticipated results. There are different ways to implement dynamic SQL, and some are more prone to SQL injection than others.
SQL injection is an idea that malicious users can inject SQL commands into SQL Query from the input control of the page. SQL injection allows attackers with unauthorized access to delete or change sensitive data, modify SQL server settings, etc. SQL injection is the direct insertion of malicious SQL code into the input variables that are used with SQL queries.
Boolean Condition-based SQL Injection:
DECLARE @sqlQuery nvarchar(max);
DECLARE @input varchar(50) = 'thomas1234@gmail.com '' OR 1 = 1--';
SET @sqlQuery = 'SELECT * FROM [dbo].[User] WHERE Email = ''' + @input + '''';
PRINT @sqlQuery;
EXEC (@sqlQuery);
Union Operator-based SQL Injection:
DECLARE @input VARCHAR(max) = '1';
SELECT @input = @input + ' UNION ALL SELECT name from sysobjects where xtype=''u''';
DECLARE @sqlQuery nvarchar(max);
SET @sqlQuery = 'SELECT Name FROM [dbo].[User] WHERE Id = ' + @input;
PRINT @sqlQuery;
EXEC (@sqlQuery);
SQL Injection Preventions:
- sp_executesql is less prone to SQL Injection while EXEC() is prone to SQL injection. So always use sp_executesql.
- Use QUOTENAME() function for quoting the object and string.
- Sanitize all the input parameters before using them in the dynamic SQL.
- Do not accept special character like semicolon (;), comment (-- or /*), single quotes (')
- Do not build Dynamic T-SQL statements directly from user input by concatenating.
- Allow limited permission to the user.
- Used parameterized query and stored procedure whenever possible.
- Prevent leaking SQL exceptions to the outside world
- Try to use the alternative Static SQL if possible
- Trigger on table or database used in D-SQL to prevent DROP, TRUNCATE and DELETE
- Schema Bonded Objects like View or UDF also prevent object deletion or modification.
- Never convert any data or parameter from nvarchar to varchar, because any character in the Unicode string that is not available in the smaller character set used for varchar is replaced with a fallback character.
Q220. Can you give me some Real Use Cases of Dynamic SQL Queries?
There is a lot of use of dynamic SQL queries. Following are some cases where Dynamic SQL is useful:
For CSV Input Parameter: Dynamic SQL can be used to return the matching records of passed CSV values from the stored procedure by using IN and NOT IN clauses.
For Dynamic Aliasing: Dynamic SQL can be used to return the Dynamic Alias Name passed by the user for the column names.
For Dynamic Column List: Dynamic SQL can be used to return Dynamic Output column lists passed by the user.
For Dynamic Object Creation: You can create objects like databases or stored procedures at runtime by using dynamic SQL queries.
Returning data from Dynamic Source: By using DSQL you can accept object name like database name, table name, and linked-server name as input parameter and returns data from that table or database, or server.
Returning data from Dynamic Schema: By using DSQL you can accept the schema name runtime as an input parameter and returns data from the provided schema name only.
For Dynamic OrderBy and Where Filtering: By using DSQL you can use the ORDER BY clause and WHERE clause condition dynamically with different conditions.
To Be Continued Part-24...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.