December 12, 2011 at 3:11 pm
The site I'm working on is having legitimate user input rejected due to a SQL injection filter at the host level. The site has free-form text entry where sales people enter their goals. The text is somewhat stilted and full of buzzwords; "select" is surprisingly common and so are hyphens. When the user puts enough SQL keywords in his goals, the filter cuts off the server connection. The frustrated user contacts our tech support and reports that the site is down.
I'm trying to make the argument that we don't need this type of filter, but I'm not entirely certain. I've seen many approaches to preventing SQL injection, but some seem redundant. The canonical "how does SQL injection happen" example always uses a SQL string constructed on the client.
Our web site is written in ASP.NET. Every database call is a stored procedure, and every argument sent is a formal, typed parameter (uses the SqlParameter class). There is no stored procedure that contains dynamic SQL in its definition.
If that is the ONLY defense we have against SQL injection, is it sufficient?
December 12, 2011 at 4:51 pm
If you use formal parameters, and you're not concatenating user input with code, then I think you are safe for the most part. I wouldn't say that you're completely safe, but if you have code like.
...
select EmployeeName
from Employees
where EmpID = @ID
...
and @ID is a parameter that goes through a formal call, like SQLParameters, then if someone concatenated anything into the string, you're get a syntax, implicit conversion error.
There might be other ways to inject code in, but the main thing filters are looking to catch is dynamic SQL vulnerabilities with input validation. If people put in free form text, you have to allow lots of words that might be SQL Server keywords. However if you run that through a set of parameters, you shouldn't have an issue.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 12, 2011 at 5:43 pm
Stephanie Giovannini (12/12/2011)
I'm trying to make the argument that we don't need this type of filter, but I'm not entirely certain. I've seen many approaches to preventing SQL injection, but some seem redundant. The canonical "how does SQL injection happen" example always uses a SQL string constructed on the client.Our web site is written in ASP.NET. Every database call is a stored procedure, and every argument sent is a formal, typed parameter (uses the SqlParameter class). There is no stored procedure that contains dynamic SQL in its definition.
And as long as you use no under the hood code to create local dynamic SQL (for example, a catch-all correction query) that doesn't re-use parameterization, you're good to go. It's not the ONLY hole to SQL Server, but it's the most likely to be attempted from a front end. Those filters probably went into place before they had a DB Developer on staff and were building their SQL queries right in the front end via concatonation.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 12, 2011 at 6:06 pm
In addition to the two answers above you may want to read
http://msdn.microsoft.com/en-us/library/ms161953.aspx
The above contains some sample injection attack methods and examples.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply