SQL Server Prepared Statement

By: Armando Prato

Overview

We've examined logical and physical design. Now it's time to get data out of the database. What are ways of accomplishing this?

Explanation

There are a number of methods and technologies (i.e. Hibernate, LINQ, etc.) available to issue your queries. Regardless of your chosen method, it's important to make sure that your query statements are re-usable and immune from SQL Injection attack. Issuing prepared queries or stored procedure calls (my preference) go a long way towards accomplishing these goals.

The following example contrasts a non-prepared statement vs. a prepared statement.

Consider the following application query:

This non-prepared query bloats the plan cache with numerous single queries as follows:

More disconcerting about the WHERE clause is that queries like these are ripe for SQL injection. Via a UI, a rogue user could potentially append a SELECT, DELETE, INSERT, or UPDATE statement to a variable expecting string input causing a severe security breach.

Now consider the query written as a prepared statement as follows:

The prepared query caches an execution plan at first run and all subsequent executions of the query re-use this plan. Furthermore, the potential for SQL injection is dramatically reduced.

Since cached plans are stored memory, it's easy to see how a reduction in your application's memory footprint can easily be achieved.

Stored procedures can also be used along with or in place of prepared queries to encapsulate logic and abstract data access away from the application. The benefit of this approach is that it gives some freedom to the database developer to make structural changes to the database without worrying about breaking the application. The idea here is that the stored procedure inputs and query output are consistent and the particulars of the data access become unknown to the application. Moreover, the application doesn't have to be scoured for all references to a database table that may have changed in structure.

Additional Information

• Are Your SQL Server Application Queries Wasting Memory?
• SQL Injection
• Getting started with SQL Server stored procedures