How to build a dynamic sql for execution from asp.net page

Question

Post reply

How to build a dynamic sql for execution from asp.net page

halifaxdal

SSCoach

Points: 19741
More actions
September 9, 2010 at 1:00 pm

#229208

Hi folks,
I know it is not even encouraged for using dynamic sql but I do feel I need to use that in my project, here is the scenario:
I am building a page with: 1. Textbox1 2. Button1 3. Textbox2
What I want to do is to let Admin user pass certain sql query in Textbox1 and click Button1 and return result in Textbox2.
In order to do that, I would need to build the query dynamically, it's easy for simple query like select * from tablename, however, I need to apply some filter in the query, and sometimes I even need to run other query like update or insert.
What's the best way to do this? Or what's the other approach to do the similar function?
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

halifaxdal

I would discourage your doing what you seem to imply you need to do.

What would happen if the user of your progam input into Textbox one text such as:

DROP TABLE sometablename

or

TRUNCATE TABLE sometablename

or

DELETE FROM sometablename -- leaving out the WHERE clause

Note: sometablename is a place holder for an actual table name in your databse

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

halifaxdal SSCoach Points: 19741 More actions · Answer 2

I fully understand and accept the risk, this function will be limited to most likely me only.

Too much bureaucracy for troubleshooting rule here.

Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Well, the sql command you'd want to use would be sp_executesql @sql, with @sql being whatever was typed into text1, and the send command to the connection being in button1. Now, you'll have to use whatever object you're coding in to deal with messages, errors, and result sets that return from sql server in the parser.

I'm not a professional front end coder so my 'official' knowledge ends at the sql layer... however... you're allowing someone who apparently can't login to Sql Server Management Studio (where you can type in the exact same thing and hit F5 instead of button1) to override this process.

You've mentioned you accept the risks, so, good luck to you. This is letting a fox loose in the henhouse though.

- Craig Farrell

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

halifaxdal SSCoach Points: 19741 More actions · Answer 4

What I am thinking is maybe stripping down the power by allowing only certain type like SELECT in the command textbox, I know how to use exec to run a dynamically generated query, it's really cumbersome to do the filtering. Without filtering, the analysis work is not fun. To add the filtering, like SELECT * FROM TABLE WHERE COLUMN = 'asdf', it's easy to type in that way, however, the whole string will be passed to the exec (@sql) where @sql = "SELECT * FROM TABLE WHERE COLUMN = 'ASDF'", so you see the problem here, the single quote needs to be handled properly. I am thinking how to handle the single quote easily. Any idea?

Thanks for all the security concern, I will carefully take that into consideration. I believe at least SELECT should be OK to go.

WayneS SSC Guru Points: 95460 More actions · Answer 5

Please read about SQL Injection before you implement this. It really would be best (IMO) to call stored procedures with the desired parameters.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

Or make sure that this page is using Windows Auth and only you, or other DBAs, can execute stuff.

What you can do as well is just create a stored proc like this:

create proc My PRoc

@cmd varchar(max)

as

-- error checking

exec(@cmd)

return

What I'd do in the error checking is perhaps strip out some common SQL Injection stuff, just to be sure that you don't make a mistake you'll regret. I might include a few statements like

select @cmd = replace( @cmd, 'shutdown', ' ')

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

At the very least even if you stick with dynamic sql make it parameterized to help minimize the risk.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jack-1089294 SSC Rookie Points: 40 More actions · Answer 8

I really hate to ask this, but. I use tons of dynamic SQL in my Code Behind Pages in asp.net. Nothing is exposed to the clients, I don't use cookies, and any query string passed between pages are just encrypted integers. Everything else is in the generated .DLLs and some Session Variables. I use Microsoft AntiXss for cross scripting. My datasets are all returned from a single Function, and INSERTS, UPDATES and DELETES are handled in a Seperate Function in a single class.

I've tried to actually attack my own application by putting everything from Javascript to Malious SQL in text boxes. All that ever happens is the application blows up.

Every tool I used says I have a vunerability to SQL Injection, but I always fail at verifying through testing.

Is there something I don't understand about SQL Injection and how to test it?

Thanks so much.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Well it is very hard to break into your own application because you know where the holes are. I know that sounds a bit silly but trust me I have seen some incredibly devious schemes from people who have nothing better to do than to mess with other people. Even if your query strings are encrypted and you use those to build dynamic sql you are at risk. I assume that the encrypted value needs to be decrypted? If you gather a few encryption results for known values it can reverse engineered (and not on your server so you are none the wiser). Then you can generate a malicious value that will properly be decrypted by your code and voila. Although it is not the complete solution to prevent sql injection but simply making ALL queries at the very least parameterized goes a LONG LONG LONG way to helping. It is very little extra code to make them parameterized. You are spending a lot of time and effort to validate against XSS and many other things. Take the few minutes it takes to convert you queries to parameterized and it will save you tons of time in the long run. This is one of things kind of like putting a value on network security. "Nobody has broken into the network so why should we pay these guys?". The first time somebody successfully attacks your site and you have to spend the next 2 weeks trying to straighten it out you will be kicking yourself for not taking the extra time to parameterize dynamic queries. Sorry for the long winded response. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jack-1089294 SSC Rookie Points: 40 More actions · Answer 10

Thanks for the information. I'm still curious. I'm putting in parameters, it's going to be a long job. 🙂