BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
Posted by Andrew Gould on 22 April 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.
What are 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.
Protecting Yourself from 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.