I know that you can execute a stored proc with the "execute" command, but is there any way to execute a string that is an sql string?
On a related note, is there a better way than building a string like "select * from table where" and then tacking on "value="+@.somevalue to do a search if your search is based on multiple (possibly null) variables? If you can answer the first question, dont bother with the second. =P
Its called dynamic SQL (or D-SQL).
declare @.sql varchar(max)
SET @.sql = 'SELECT * FROM TABLE WHERE NAME LIKE @.searchstring ORDER BY NAME' [or something like that]
EXEC sp_executesql @.sql, N'@.searchstring varchar(50)', @.searchstring
Now you have to be carefule about SQL Injection, which means that someone out there may try to insert SQL of their own like -- or WHERE 1=1 or other things that can force errors in your D-SQL, which report errors to the user which include things like table and column names. Which they can the use to get to your security tables or what have you. It can be very dangerous.
http://www.sommarskog.se/dynamic_sql.html
|||Dynamic sql can be executed with thesp_executesql command however, if you know what possible columns the user could search on then it's best to use a CASE statement and avoid dynamic sql. If multiple columns may or not be searched, you can also useoptional parameters.
There was a really ugly thread on that a few days ago and in the end the guy used D-SQL. Do you have a functioning example?
|||I've never encountered a situation where I've needed to use D-SQL. Just start with the idea that you CANNOT use D-SQL. Like was said above, you can find a way using CASE statements in most cases. Why don't you post the stored procedure and maybe people can help you so you don't need to use D-SQL?
|||Its not mine.
http://forums.asp.net/t/1163791.aspx
I think that there is a time and place for D-SQL. If you have a system that stores credit card numbers and stuff like that, well, yeah, no way. But if you don't have sensitive information in your DB, or if you can isolate your sensitive information on a seperate DB and maybe do joins through objects, D-SQL is safe enough. Starting with the idea that you can't use D-SQL no matter what is similar to a physician being told he can't use a particular medicine because some folks have died from overdoses of it.
I work for a gov't contractor on secure systems, and most of our work is framework development, so I haven't had to deal with the issue of the best and safest way to write a full text indexed search function. Its coming up though. But if I had the choice of writing 1 D-SQL routine and locking it down or writing 1,000 stored procedures that were table-specific, I think I'll go with D-SQL. Of course, I have the luxury of security testers who can be assigned to try to crack my code, and I realize that many of the folks here are one-man shows.
Now, it occurs to me that if hackers didn't get feedback through the .Net framework on the client when they forced SQL errors, SQL injection would be almost impossible. Is there some way to use compiler directives or attributes or something to prevent unhandled exceptions from bubbling up? Maybe replace it with a nice uninformative dialog box telling folks not to format their search strings in naughty ways and if they do it again they get their IP blocked?
|||I learned to love dynamic SQL after I wrote a bunch of stored procedures using D-SQL and then ran into a problem that I had to try to track down and troubleshoot.. It's worth the extra time to spend to learn to do things the right way.
|||I have seen a lot of novices fal into the trap. Things that are easiest to learn can get you into trouble that quickly too. D-SQL can be effective if used wisely. Same goes with CURSORs, TRIGGERs. It is very easy to write up a CURSOR than to figure out how to write a loop without CURSOR so novices fall into the trap and start using CURSOR for the simplest of the loops. I have seen people asking how to execute DTS package via TRIGGER. That was a new high for me.. There is always more than one way to skin a cat. Its worth the extra time to read up the best practices (even a quick google search brings up plenty of articles) and implement them and pays in the long run.
|||Do you think we should start a D-SQL forum here? It seems like there's a lot of people wanting to know more about it and some people who are downright alarmed by it. Like fire, it is a wonderul servant and a terrible master...
|||Erland has an excellent article:The Curse and Blessings of Dynamic SQL. Its long but worth reading.
|||Yeah, you sent that to me 2 weeks ago. It was very good. Thanks again.
sql