562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Part four of a four-part series of blogs
Dynamic SQL is a technique for building valid SQL statements from separate pieces of text. You can use this technique to create remarkably flexible and useful queries, as long as you're aware of the potential danger of SQL injection attacks.
So far this blog series has taught you several ways to create and use dynamic SQL statements. Before you start using this technique in the real world however, you need to be aware of a potential danger: SQL injection attacks.
Injection attacks occur when a user passes a value to a dynamic SQL statement in an attempt to execute actions that you didn't intend them to perform.
Imagine you had a text box on a website, much like the one at the top of this page, to allow users to search your site. Clicking a button might send the value of the text box to a procedure which builds a dynamic SQL statement designed to retrieve matching records and present them to the user, something like the procedure shown below:
CREATE PROC spSearchBlogs
DECLARE @SQLString VARCHAR(MAX)
SET @SQLString =
'SELECT BlogName, BlogURL FROM tblBlog
WHERE BlogName LIKE ''%' + @SearchString + '%'''
Now imagine that a user types the following into the search box:
a'; DROP TABLE tblUsers--
The a' simply passes the letter a into the search string and closes the quotes to complete a valid search string. The semi-colon which follows this is used as a statement terminator to end the query. The next part of the text begins a new SQL statement and attempts to delete our table of users! The final two dashes are used to begin a comment which effectively disables any other code that we might be concatenating to the end of the search string.
Frightening isn't it? Although it can also be humorous. There are many other things a malicious user might attempt to do and many professionals make a good living from attempting to break a client's system with an SQL injection attack.
There are many techniques for preventing SQL injection attacks, some of which may be taken care of by people involved in developing other parts of your complete system. You should still be aware of the things that you can do to help mitigate the chances of such an attack succeeding. There are several excellent websites which describe various strategies for dealing with SQL injection, including this excellent article from the CodeProject, and the definitive article on dynamic SQL by Erland Sommarskog. Microsoft even have a free tool which you can download to help identify SQL injection vulnerabilities in ASP.NET code.
|Parts of this blog|
25 Aytoun Street