Passing Wildcard parameters through Query

Question

Post reply

Passing Wildcard parameters through Query

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 21, 2014 at 2:12 pm

#281443

I am trying to pass wildcard parameter through the Query instead of filters.
Select FName,Lname from Customers
where ID = @ID
The above works fine. Now when I try with wildcards I get blank row..
I tried
Select FName,Lname from Customers
where ID = '%' + @ID+ '%'
Any ideas?

Viewing 12 posts - 1 through 12 (of 12 total)

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

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 1

For your wildcard search portion, you need to use LIKE instead of the = operator; so:

Select FName,Lname from Customers

where ID LIKE '%' + @ID+ '%'

By using = instead of LIKE, you're looking specifically for %@ID% (replacing the value of @ID); using LIKE causes the wildcards to be interpreted correctly.

I'd be careful of using this sort of query, though; without the proper proofing, it's open to SQL injection, and all of its assorted security risks. EDIT: Or not at all. I should probably use a little more comprehension!

-

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

Yes, I see that. But how can I also see all the values through wildcard.

I mean I should be able to pass "*" and see all aswell

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

hisakimatama (1/21/2014)
I'd be careful of using this sort of query, though; without the proper proofing, it's open to SQL injection, and all of its assorted security risks.

Can you explain how can this be used for SQL Injection? Unless the query becomes dynamic, concatenating values to a variable used in a like comparison won't generate SQL Injection.

DECLARE @ID varchar(8000) = '1; SELECT * FROM sys.databases;'

Select *

from SomeTable

where table_id LIKE '%' + @ID+ '%'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 4

It will be passed through a dataset so the values will be entered at runtime.

So, I wont declare in the dataset...

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 5

sharonsql2013 (1/21/2014)
Yes, I see that. But how can I also see all the values through wildcard.
I mean I should be able to pass "*" and see all aswell

Hm. If you specifically want to pass an asterisk, it'll require a check to see if an asterisk was entered, and then blanking out @ID; this will make you do a search of LIKE '%%', which will wildcard-return all values. That would be the simplest way, if a bit shaky.

Luis, after a bit of tinkering in SSRS myself, I've clearly misspoken! I suppose I've got another complaint to lodge with our vendor, regrettably. Threw together a basic report and tried to injection-drop a dummy table, and nothing happened. Tried to injection-drop via a vendor report... Success! Not a good thing! I'll retract that earlier point.

-

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

hisakimatama (1/21/2014)
Luis, after a bit of tinkering in SSRS myself, I've clearly misspoken! I suppose I've got another complaint to lodge with our vendor, regrettably. Threw together a basic report and tried to injection-drop a dummy table, and nothing happened. Tried to injection-drop via a vendor report... Success! Not a good thing! I'll retract that earlier point.

Kill the vendor!... Ok, a complaint is fine for now.

Good luck!

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 7

Select *

from SomeTable

where table_id LIKE '%' + @ID+ '%' OR table_id LIKE '%'

Well, it always give all values irrespective of parameter.

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 8

Here is what I was looking for

Select *

from SomeTable

where (table_id LIKE '%' + @ID+ '%' OR @ID = '*')

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

sharonsql2013 (1/21/2014)
Select *
from SomeTable
where table_id LIKE '%' + @ID+ '%' OR table_id LIKE '%'
Well, it always give all values irrespective of parameter.

That's because "OR table_id LIKE '%'" makes your WHERE clause to evaluate as true for any rows.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 10

sharonsql2013 (1/21/2014)
Here is what I was looking for
Select *
from SomeTable
where (table_id LIKE '%' + @ID+ '%' OR @ID = '*')

Terribly sorry for the late reply; I left work shortly after my last one, and got a bit tangled up afterwards.

In any case, what you're looking for is to give your users the ability to enter an ID to pull that specific ID, and an asterisk to pull everything, correct? If so, it could be accomplished like so:

IF @ID = '*'

SELECT * FROM SomeTable

ELSE

SELECT * FROM SomeTable WHERE table_id LIKE '%'+@ID+'%'

Alternatively, you could replace the IF @ID check's asterisk with anything; I usually use "All Items" or something similar in my reports, so it's more intuitive for the users.

-

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 11

sharonsql2013

SSCertifiable

Points: 5908

January 27, 2014 at 7:32 pm

#1683775

Thank you.

Passing Wildcard parameters through Query

Cookies on SQLServerCentral