Slide 1 : SQL SERVER 2008 DYNAMIC SQL
Slide 2 : Dynamic SQL describes any SQL DML statement assembled dynamically at runtime as a string and then submitted. DYNAMIC SQL
Slide 3 : 1. Multiple possible query criteria can be dynamically assembled into custom FROM, WHERE, and ORDER BY clauses for flexible queries.
2. Code can respond to the schema of the database and generate appropriate triggers, CRUD stored procedures, and views.
3. Dynamic code can auto-generate very consistent stored procedures Uses of Dynamic SQL
Slide 4 : 1. Dynamic SQL that includes user entries in WHERE clauses can be open to SQL injection attacks.
2. Poorly written dynamic SQL queries often include extra table references and perform poorly.
3. T-SQL code that generates T-SQL code can be tricky to debug. Issues when developing dynamic SQL:
Slide 5 : The EXECUTE command, or EXEC for short, creates a new instance of the batch as if it were a batch submitted from some client to the server. While the EXECUTE command is normally used to call a stored procedure, it can also be used to execute a T-SQL query or batch: Executing Dynamic SQL
Slide 6 : For example, the following EXEC command executes a simple SELECT statement:
Slide 7 : Example:
Slide 8 : sp_execute SQL Another method of executing dynamic SQL is to use the sp_executesql system stored procedure. It offers greater compatibility with complex SQL queries than the straight EXECUTE command.
Slide 9 : Parameterized queries Parameters provide optimization. If the T-SQL query has the same parameters for each execution, then these parameters can be passed to sp_executesql so the SQL query plan can be stored, and future executions will be optimized.
Slide 10 : Building a dynamic SQL string usually entails combining a SELECT column’s literal string with a more fluid FROM clause and WHERE clause. While any part of the query can be dynamic, normally the SELECT @columns is not.
Once the SQL string is complete, the SQL statement is executed by means of the sp_executesql command. The example that follows builds both custom FROM and WHERE clauses based on the user’s requirements. Developing dynamic SQL code
Slide 11 : Real-world dynamic SQL sometimes includes dozens of complex options. The following code listing uses three possible columns for optional user criteria: Example:
Slide 12 : Example:
Slide 13 : Example:
Slide 14 : The results shown are both the printed text of the dynamic SQL and the data returned from the execution of the dynamic SQL statement: RESULTS:
Slide 15 : Preventing SQL Injection SQL injection is a hacker technique that appends SQL code to a parameter that is later executed as dynamic SQL. What makes SQL injection so dangerous is that anyone with access to the organization’s
website who can enter data into a text field can attempt an SQL injection attack. There are several malicious techniques that involve appending code or modifying the WHERE clause.
Slide 16 : Adding a statement terminator, another SQL command, and a comment, a hacker can pass code into the execute string.
For example, if the parameter passed in is Preventing SQL Injection
Slide 17 : Other popular appended commands include running xp_commandshell or setting the sa password. Preventing SQL Injection
Slide 18 : Another SQL injection technique is to modify the WHERE clause so that more rows are selected than intended.
If the user enters the following string into the user text box: 123’ or 1=1 --
Slide 19 : Another creative use of SQL injection is to comment out part of the intended code. Suppose the user enters the following in the web form: Password? What password?
Slide 20 : The comment in the username causes SQL Server to ignore the rest of the WHERE clause, including the password condition. The resulting SQL statement might read as follows:
Slide 21 : Preventing SQL Server injection attacks Several development techniques can prevent SQL injection:
1. Use EXECUTE AS and carefully define the roles so that statements don’t have permission to drop tables.
2. Use DRI referential integrity to prevent deleting primary table rows with dependent secondary table rows.
3. Never let user input mixed with dynamic SQL in a web form execute as submitted SQL. Always pass all parameters through a stored procedure.
4. Check for and reject parameters that include statement terminators, comments, or xp_.
5. Test your database using the SQL injection techniques described above.
Slide 22 : If your application is exposed to entry from the Internet and you haven’t taken steps to prevent SQL injection, it’s only a matter of time before your database is attacked. SQL injection is a real threat
Slide 23 : Summary Build up the @SQLStr variable from the inside out — start with the dynamic list and then append the SELECT prolog.
If the WHERE clause is dynamic, chances are good the FROM clause will be also.
2. When writing dynamic SQL, add a PRINT statement to output the @SQLStr variable during development. It makes debugging much easier.
Use sp_executesql.
4. If there’s a different way to make the code flexible, such as the parse and join method mentioned in the best practice, do that instead of dynamic SQL.
Slide 24 : All the standard database integrity features (e.g., foreign keys) help defend against SQL injection.
Always think like a hacker. Where can an SQL injection string be used to alter the intention of the code?
7. Dynamic SQL is not necessarily ad-hoc SQL. Never permit ad-hoc SQL to your database. Summary
Slide 25 :
Slide 26 :
Slide 27 :
Slide 28 :
Slide 29 :
Slide 30 :
Slide 31 :
Slide 32 :
Slide 33 :
Slide 34 :
Slide 35 :
Slide 36 :
Slide 37 :