Evaluate special programming constructs.
This objective may include but is not limited to: dynamic vs. prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protecting against SQL injection
For this requirement we will be interested in looking at the differences between Dynamic SQL and Prepared Statement SQL. However, since the Prepared SQL is marked with the addition of CREATE PROCEDURE … WITH EXECUTE AS I believe that we should be considering the implications of running the Dynamic SQL or Prepared SQL within a Stored Procedure. Finally, we will take a quick look at the VERY broad bullet point of Protecting Against SQL Injection (A subject for which many complete books have been written).
The Curse and Blessings of Dynamic SQL by Erland Sommarskog is a great source for this requirement. This article does a much better job in much more depth than you will find here.
Dynamic vs Prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) Procedure
Dynamic SQL is useful in interacting with data in a fashion that is unknown at the time the application is being developed. Dynamic SQL is built using a command string which is altered to provide the specific criteria for the calling instance . This gives very great flexibility in the data access layer, allowing for only the information that is needed at the time the call is being made to be returned, or only filtering where appropriate. However, Dynamic SQL comes with the cost of opening up the potential for SQL Injection as well as needing to go through the full cycle for query plan composition for every execution.
Prepared Statements use a command string which is not altered from run to run. If there is a portion of the string which would need to be modified between runs, it is parameterized and the input value is inserted via this parameter. This allows the query optimizer to reuse the execution plan (See Execution Plan Caching and Reuse). Note that certain actions are not available with Prepared Statements, such as parameterizing a result set’s column list or a table name.
CREATE PROCEDURE … EXECUTE AS
Note that with Dynamic SQL, Ownership Chaining is broken as the dynamic SQL block is executed as though it were a stored procedure, which does not itself have an owner. When creating a procedure with the EXECUTE AS clause, or Signed Procedures with Certificates, you can provide the proper level of security to the procedure to execute under.
Protecting Against SQL Injection
One of the most important issues to deal with when administering an SQL server is to protect the application against SQL Injection. First and foremost, the principle of least privileges should be applied. However, that is more of a general rule than anything that is specifically dealing with SQL Injection. Next, you should attempt to validate all user input. You should almost certainly be escaping input with routines such as QUOTENAME and REPLACE. Furthermore, you should use parameterized statements.
See the following posts, articles and B.O.L. entries for more information: