mssqltips logo

SQL Server Prepared Statement



By:

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:

make sure that your query statements are re-usable and immune from SQL Injection attack

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

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:

More disconcerting about the WHERE clause is that queries like these are ripe for SQL injection

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

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


Last Update: 9/10/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools