SQL Parameterized Query multiple values without modifying database

Question

My current solution is along the lines of

SELECT * FROM TableName WHERE ID in (1, 2, 3)

This cannot be translated to

SELECT * FROM TableName WHERE ID in (@IDs)

The connection to the database cannot make temporary tables or functions. Is there any way to perform a parameterized query with multiple values in a single statement without modifying the database?

The reason for parameterizing the query is that not every case will be searching for a numeric field. Therefore the values that are received can contain any text. Is there any way to replace special characters in the input so that there is no risk of returning information the user should not have access to?

EDIT: More Info

SELECT *
FROM [dbo].[Parts]
WHERE IsDeleted = 0
    AND [PartNumber] IN (@PartNumbers)
ORDER BY [PartNumber] ASC

In the above SQL the query is read from a text file and groovy is used to replace @PartNumbers with 'PartNumber1', 'PartNumber2'

But the value the groovy script writes is an inbound message property. So in this case the user made a GET Rest request /myservice?partnumbers=PartNumber1,PartNumber2 this is easy to inject, if the parameter was partnumbers=PartNumber1' or '% then the result would be

SELECT *
FROM [dbo].[Parts]
WHERE IsDeleted = 0
    AND [PartNumber] IN ('PartNumber1' or '%')
ORDER BY [PartNumber] ASC

Which would return too much information.

I'd like to add that only authenticated users can use the service and they cannot make edits to the database. — Adam, May 10 '17 at 18:14
Unless you show all the relevant code, it's hard to help. How is `@ID` being populated? Include the domain for possible values contained in `@ID` — Alfabravo, May 10 '17 at 18:16
Can you programatically create the SQL as needed? In other words, instead of using parameters at all, just do "SELECT * FROM TableName WHERE ID IN('id1', 'id2')", and then just fire the resulting string against the DB. — DanielG, May 10 '17 at 18:18
@DanielG that is what I'm currently doing, but what if the parameter they requested is `PartNumber1' or 1 = 1` — Adam, May 10 '17 at 18:23
Why not pass 1 parameter value per parameter? After all, that is really what it should be. Example: `AND [PartNumber] IN (@PartNumber1, @PartNumber2, @PartNumber3, @PartNumber4)` — Igor, May 10 '17 at 18:26
Instead of multiple parameters which can easily exceed the length you should look into table valued parameters. It can hold as many or few values as you want and the code will remain constant. — Sean Lange, May 10 '17 at 18:29

score 1 · Accepted Answer · edited May 23 '17 at 10:31

A parameter is a value placeholder. If you want to compare/use multiple values in your query you should use multiple parameters.

Example:

AND [PartNumber] IN (@PartNumber1, @PartNumber2, @PartNumber3, @PartNumber4)

If the number of parameters can be excessively large consider using a table valued parameter instead. Here is a link to a previous SO answer about that in c#, I would provide more but I am not sure what you are writing in. Parameterize an SQL IN clause

score 0 · Answer 2 · answered May 10 '17 at 18:38

I had a similar scenario a while ago, and I solved it in a fashion similar to the one in the SO link which @Igor provided.
What I did was to pass all of the values in a single delimited string, though on the back end I had SQL Server break that string apart by the delimiters and insert the different parts into a temp table. I then joined the temp table to the table in question with astounding performance.

If you provide some data we can show you how it is done.

Good luck!

SQL Parameterized Query multiple values without modifying database

2 Answers2