Passing Wildcard parameters through Query

  • 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?

  • 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!

    - 😀

  • 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

  • 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
  • It will be passed through a dataset so the values will be entered at runtime.

    So, I wont declare in the dataset...

  • 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.

    - 😀

  • 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
  • Select *

    from SomeTable

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

    Well, it always give all values irrespective of parameter.

  • Here is what I was looking for

    Select *

    from SomeTable

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

  • 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
  • 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.

    - 😀

  • Thank you.

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

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