September 24, 2008 at 6:14 am
I have an asp.net form for searching customers that has very detailed searching and sorting capabilities. The sql for that powers it is all dynamic with input parameters to help prevent sql injection. Im not sure if it would totally stop the injection because of the dynamic sql. Now my client wants me to add a "where box" to the form. That way they can just add a bunch of sql to the query for a much more detailed result set. This is great for them, but what about someone who wants to add a delete or truncate to the query? If I add the entire contents of the "where box" to a parameter will sql stop all of the malicious code injections but allow any sort of in selects, case, counts, joins and other type functions to be used? Thanks for any help!
Ryan
September 24, 2008 at 6:25 am
Hi Ryan,
Allowing free text in to Dynamic SQL; a crazy request!! :crazy:
As soon as you start using Dynamic SQL you open that whole injection can o' worms. The security factor alone should make the client realise how far past wrong this is.
I would try and steer the client away from such a box and maybe towards a WHERE clause builder whereby the user can (via radio buttons) only select from the fields available, the operator they want to use for comparison and then the value for the comparison.
This way you can validate the comparison value before it goes anywhere near the execution statement. If you only expect numerics, only accept numerics etc.
If this is really necessary you'd have to build a parser to ensure that there are no deletes, truncates, or whatever in the code before passing it to be executed.
September 24, 2008 at 6:36 am
It is necessary in this case. I was thinking about a module to parse out all the bad keywords like delete, truncate, ect.. But isn't there a way to use hex codes or something to send in those words? Also, can they be split and resembled by sql as a string or something like that?
Ryan
September 24, 2008 at 6:56 am
There are ways to perform SQL injection using hex codes, and in my honest opinion, this where box sounds about as dangerous as it gets.
http://www.webappsec.org/projects/threat/classes/sql_injection.shtml
has some white paper links about SQL injection.
September 24, 2008 at 7:22 am
On the bleeding edge of disaster is the place we are going to be I guess! My client has a business application that Ive seen personally from a large software vendor (no names mentioned) that has the same type where box.
September 24, 2008 at 7:25 am
I'm pretty sure that given this requirement I would protest as loudly as possible, and even get it in writing that you disagreed with this requirement and you have explained all of the security implications that something like this would imply. I'd try and get the CEO, CTO, CIO or whomever to sign off on this prior to implementation.
OF course you could always offer other suggestions, or sit down with them and try to figure out exactly why they think they need this. Could another report work just as well? Could you create a model in Reporting services that would allow them ad hoc query access without some of the blatant security risks etc.
A lot of this depends on where the site resides, is it an internal intranet, and extranet or hanging off their public website. How is the security of the connection handled? What types of access is granted to the users using this form? read only, R/W ? I'd certainly hope not DBO.
If they still insist on it, I'd spend a decent amount of their money recreating the graphical query tool in SSMS or something like it. Make them select various options that would build a where string that they would not be able to alter directly except through the graphical interface you build. I'd also do checking for certain reserved words, particularly Cast and Convert (which were the two used this summer to do all the nastiness), and make sure there are no "--" or any other Comment characters and Semicolons.
Another bit of ammo for trying to persuade them to go another route might be, who will be creating the queries? Will it be someone with a clue of how easily they can create a "query from hell" and take down the db or will it be people with no training at all?
So I'd try to stay away from this by offering other suggestions and trying to find the root business reason for this requirement, then if all else fairs get a decent amount of CYA literature (probably want to have a lawyer look at it just in case), and then once it would be implemented in the safest possible way, I'd see if I couldn't find a way to terminate the business relationship with that client. If they don't care about their company's data security why should you? It's better to lose one client than lose them all when it goes south and they start flaming you like crazy.
-Luke.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply