This page has 0 threads | Add post

SQL Injection Attacks

Written by Andrew Gould

In this tutorial

What are Injection Attacks?
Protecting Yourself from Attack

So far this tutorial on dynamic SQL 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

(

@SearchString VARCHAR(MAX)

)

BEGIN

DECLARE @SQLString VARCHAR(MAX)

SET @SQLString =

'SELECT BlogName, BlogURL FROM tblBlog

WHERE BlogName LIKE ''%' + @SearchString + '%'''

EXEC (@SQLString)

END

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.

Previous
tutorial

Triggers

Previous
part

Building Dynamic SQL Statements

Top of page

Next
part

Next
tutorial

Transactions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Dynamic SQL

SQL Injection Attacks

What are Injection Attacks?

Protecting Yourself from Attack

Head office

London

Manchester