Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2. You can visit his website and blog at TimMitchell.net or follow him on Twitter at twitter.com/Tim_Mitchell.

SQL Injection... is this still a problem?

For some reason, today I came across at least a half-dozen blog posts and articles about how to avoid SQL injection attacks.  It had been a while since I had read about SQL injections, and an odd thought comes to mind:

Why is SQL injection still an issue?

This is such an easy problem to get around.  The use of stored procedures or parameterized queries will wholly eliminate this threat, and is just as easy (in many cases, easier) than building dynamic SQL.  I have a no-exceptions rule that I never allow dynamic SQL in production code.  I have built a few prototypes for demonstration purposes that use dynamic SQL, but in those cases I usually want to actually demonstrate the SQL query as much as the app itself.  For live systems, using stored procedures prevents these attacks and can yield some performance gains as well by persisting the execution plan.  If you require more flexibility, you can use parameterized SQL from .NET applications.

Please forgive this soapbox rant about this issue, but it seems pretty clear to me.  Smoking causes cancer, drinking and driving causes death, and dynamic SQL causes injection attacks.  All three are bad and completely avoidable.

Comments

Posted by Richard on 16 October 2006
Stored procedures will not prevent SQL Injection problems unless they are used properly.

For example, this psuedo-code would be open to SQL Injection:

query = "exec GetData @UserName='" + name + "'"
connection.Execute(query)

Whereas this wouldn't:

query = new Command()
query.Text = "exec GetData @UserName = @UserName"
query.AddParameter("@UserName", name)
query.Execute()
Leave a Comment

Please register or log in to leave a comment.