Passing conditional where clause ?

Question

Post reply

Passing conditional where clause ?

vchandm23

Old Hand

Points: 314
More actions
July 25, 2012 at 9:37 pm

#278546

Hi All,
Now in the SP shown below, I want to control the where clause from outside (Application).
Create Proc (@WhereClause VARCHAR(1000))
AS
SELECT * FROM Person.Contact
WHERE @WhereClause
From the application I want to determine the where clause combination.
Reason is simple I know before hand on which column I have to search the data on Full text indexed table.
For example : If my application passes @WhereClause = "Contains(Firstname,'Christopher') AND Contains (Lastname,'NOLAN') AND
Contains(City,'Gotham')"
Then my stored procedure should apply this @WhereClause in the WHERE portion of the statement specified in the SP.
I know this can be achieved by building the script from the application and passing the select script as we do in OLD school ADO.NET way. I dont prefer doing that! 🙂
FYI: The COALESE can't be used also because, the free text indexed columns doesn't allow empty strings?
Is this possible to achieve, have anyone faced this kind of situation? If so, please share your thoughts.
Cheers,
Chandra.

Viewing 8 posts - 1 through 7 (of 7 total)

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

vchandm23 Old Hand Points: 314 More actions · Answer 1

CREATE PROCEDURE [dbo].[myProc]

@whereSql nvarchar(256)

AS

EXEC('SELECT [fields] FROM

WHERE ' + @whereSql)

GO

From stackOverflow website. This is what i was looking for. I am sharing this here because I want to make sure the information is shared to newbies like me.

Cheers,

Chandra.

CapnHector SSCoach Points: 16143 More actions · Answer 2

vchandm23 (7/25/2012)
CREATE PROCEDURE [dbo].[myProc]
@whereSql nvarchar(256)
AS
EXEC('SELECT [fields] FROM
WHERE ' + @whereSql)
GO
From stackOverflow website. This is what i was looking for. I am sharing this here because I want to make sure the information is shared to newbies like me.
Cheers,
Chandra.

NOOOOOOOOO. that opens you up to sql injection and can have bad bad results. I never just blindly add what some user provides and most of the time if my SP's are dynamicly created the input options just drive the if statements in the query creation and are not added to the query. if they are added to the query they are checked double checked and if any thing is remotly off it gets tossed and an error is returned.

It will be more complex but alot more secure if you take the time to design your program and SP so the program passes simple values then your SP creates the query.

EDIT: here is a great resource on dynamic SQL http://www.sommarskog.se/dynamic_sql.html

and here is the section on SQL Injection specifically http://www.sommarskog.se/dynamic_sql.html#SQL_injection

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

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

capn.hector (7/26/2012)
vchandm23 (7/25/2012)
CREATE PROCEDURE [dbo].[myProc]
@whereSql nvarchar(256)
AS
EXEC('SELECT [fields] FROM
WHERE ' + @whereSql)
GO
From stackOverflow website. This is what i was looking for. I am sharing this here because I want to make sure the information is shared to newbies like me.
Cheers,
Chandra.
NOOOOOOOOO. that opens you up to sql injection and can have bad bad results. I never just blindly add what some user provides and most of the time if my SP's are dynamicly created the input options just drive the if statements in the query creation and are not added to the query. if they are added to the query they are checked double checked and if any thing is remotly off it gets tossed and an error is returned.
It will be more complex but alot more secure if you take the time to design your program and SP so the program passes simple values then your SP creates the query.
EDIT: here is a great resource on dynamic SQL http://www.sommarskog.se/dynamic_sql.html
and here is the section on SQL Injection specifically http://www.sommarskog.se/dynamic_sql.html#SQL_injection

+100000

I could not agree more with Capn. DO NOT USE the process suggested. Chandra, you should not use that code ever. Read the articles from capn and take heed.

_______________________________________________________________

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/

CapnHector SSCoach Points: 16143 More actions · Answer 4

to actually be helpful im going to point you to another link. again from sommarskog.se

http://www.sommarskog.se/dyn-search-2008.html

it addresses variable search conditions in a very well thought out manner.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

Sean Lange (7/26/2012)
capn.hector (7/26/2012)
vchandm23 (7/25/2012)
CREATE PROCEDURE [dbo].[myProc]
@whereSql nvarchar(256)
AS
EXEC('SELECT [fields] FROM
WHERE ' + @whereSql)
GO
From stackOverflow website. This is what i was looking for. I am sharing this here because I want to make sure the information is shared to newbies like me.
Cheers,
Chandra.
NOOOOOOOOO. that opens you up to sql injection and can have bad bad results. I never just blindly add what some user provides and most of the time if my SP's are dynamicly created the input options just drive the if statements in the query creation and are not added to the query. if they are added to the query they are checked double checked and if any thing is remotly off it gets tossed and an error is returned.
It will be more complex but alot more secure if you take the time to design your program and SP so the program passes simple values then your SP creates the query.
EDIT: here is a great resource on dynamic SQL http://www.sommarskog.se/dynamic_sql.html
and here is the section on SQL Injection specifically http://www.sommarskog.se/dynamic_sql.html#SQL_injection
+100000
I could not agree more with Capn. DO NOT USE the process suggested. Chandra, you should not use that code ever. Read the articles from capn and take heed.

Can I do something like this in the procedure (just striked my mind):

IF (

SELECT SUM(CHARINDEX(name, @whereSql)) idx

FROM sys.all_columns

WHERE object_id = object_id(<table>)

) <> 0

EXEC('SELECT [fields] FROM

WHERE ' + @whereSql)

The IF statement evaluates that @whereSqql should use one of the column name from the table. Usually I have seen that for SQL Injections we use something like

WHERE 1=1;--

As the IF condition for this will not be equal to 0 (zero), query will not be executed.

I am sure this is not 100% foolproof way. But I am expecting comments/suggestions on this.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

CapnHector SSCoach Points: 16143 More actions · Answer 6

I would look at http://www.sommarskog.se/dyn-search-2008.html as it describes exactly what you want to do. it also offers several ways to accomplish the task which are safe from sql injection if used properly.

after reading that if you still have questions let us know.

the way you are going about doing this is still open to the risk of sql injection as you are directly adding the user provided paramater to the executed sql string.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Lokesh Vij SSChampion Points: 10836 More actions · Answer 7

Wonderful article Capn. Thanks for sharing.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter