Select IN using varchar string with comma delimited values

Question

I am trying to search several tables for a list of phones.

The problem is converting the single string into a valid comma delimited string to use in conjunction with the IN clause.

I tried using replace to fix the problem.

DECLARE @PhoneNumber VARCHAR(3000) 
SET @PhoneNumber = '6725556666,2124444444'
SET @PhoneNumber = '''' + @PhoneNumber + ''''

SELECT @PhoneNumber
'6725556666','2124444444'

Finally the sample SQL does not recognize the string as expected:

SELECT  Provider
        ,PhoneNumber      
        ,ChangeType
        ,ChangeDate 
FROM dbo.PhoneLog
WHERE PhoneNumber IN (@PhoneNumber)

possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — bummi, Nov 19 '14 at 16:51
Internet Engineer? Search! http://sqlperformance.com/2012/07/t-sql-queries/split-strings & http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql — Aaron Bertrand, Nov 19 '14 at 18:28

jpw · Accepted Answer · 2014-11-19T19:05:35.593

There are several ways to handle this. One option is to use dynamic sql and inject your phone number string into a variable containing the statement and then executing that like this:

DECLARE @PhoneNumber VARCHAR(3000) 
SET @PhoneNumber = '6725556666,2124444444'
DECLARE @SQL NVARCHAR(max)
SET @SQL = N'
    SELECT Provider, PhoneNumber, ChangeType, ChangeDate 
    FROM dbo.PhoneLog
    WHERE PhoneNumber IN (' + @PhoneNumber + ')'
EXEC sp_executesql @SQL

Please note that this approach can be vulnerable to SQL injection attacks, for instance feeding a string like

SET @PhoneNumber = '1);truncate table phonelog;--'

would effectively empty the table. So using a dynamic SQL approach like above should only be an option if it's certain that the string fed that in injected is sanitized and safe (or maybe it should never be used).

Another, possibly better, option is to use a user defined function to split the phonenumber variable and use that like this:

SELECT Provider, PhoneNumber, ChangeType, ChangeDate 
FROM dbo.PhoneLog
WHERE PhoneNumber IN (
    SELECT splitdata FROM dbo.fnSplitString(@PhoneNumber,',')
    -- you could add a check here that the data returned from the function 
    -- is indeed numeric and valid
    -- WHERE ISNUMERIC(splitdata) = 1
    )

Here's the function used in the example:

CREATE FUNCTION [dbo].[fnSplitString]   
    (   
        @string NVARCHAR(MAX),   
        @delimiter CHAR(1)   
    )   
    RETURNS @output TABLE(splitdata NVARCHAR(MAX)   
    )   
    BEGIN   
        DECLARE @start INT, @end INT   
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)   
        WHILE @start < LEN(@string) + 1 BEGIN   
            IF @end = 0    
                SET @end = LEN(@string) + 1  

            INSERT INTO @output (splitdata)    
            VALUES(SUBSTRING(@string, @start, @end - @start))   
            SET @start = @end + 1   
            SET @end = CHARINDEX(@delimiter, @string, @start)  

        END   
        RETURN   
    END

I did not write the function, I think I got it somewhere on the internet...

"possibly better"? I guess you haven't met Bobby Tables yet? Please don't advocate dynamic SQL approaches that invite SQL injection exploits. — Aaron Bertrand, Nov 19 '14 at 18:29
@AaronBertrand But I have met dear Bobby Tables and I am aware of the danger, but should the assumption always be that using dynamic sql invites injection attacks? Why not assume that the input is properly sanitized before it's fed into the query? Or that the OP is aware of the risk too, or at least can evaluate it. — jpw, Nov 19 '14 at 19:00
@AaronBertrand Oh, and I assumed the criticism was in reference to the first option, but is there any problem of this kind with the function too in the second option? I don't think so, but your comment made me unsure. — jpw, Nov 19 '14 at 19:02
"assumption" should be all you need to read. This is the same assumption that continues to get so many companies into hot water. No, you can't ever assume that input is sanitized, or that the OP is aware of the risks. This could be the first SQL query the OP has ever written, on their first day on the job. Do you want to be associated with the news story that would result? — Aaron Bertrand, Nov 19 '14 at 19:05
@AaronBertrand At least I would get my fifteen minutes of fame ;-) But, yeah, point taken. — jpw, Nov 19 '14 at 19:06

Select IN using varchar string with comma delimited values

1 Answers1

Linked