Is string_split() injection safe?

user_13754016 0
Viewed 75

After reading this great article on handling multi-valued parameters, I learned about the convenient string_split() function.
I would like to use it in a where clause of a parameterized query like this:

WHERE [SomeTable].[SomeColumn] IN (
    SELECT TRIM(value)
    FROM string_split(@commaSeparatedListOfValues, ',')
)

Where @commaSeparatedListOfValues is a string received from user input and passed as a parameter.
I am wondering if the function is safe against injections. Is it really impossible to escape a string passed this way? What if the user provides a string like (naively)

"firstValue , ');PRINT 'Hello'-- , thirdValue".

Of course it is added as a parameter in the first step and can do no harm, but does this still apply after splitting it?

I know string_split() can only return tables of string types. I find little to no mention of such issues, so I guess it is a non issue?

security sql-server sql
1 Answers

There is no outright injection issue in the sense that arbitrary code cannot be executed. This is because, ultimately, @commaSeparatedListOfValues remains a string, and is never parsed into actual code.

The only injection issue is if you are explicitly passing say three value, and one of them contained a comma.

For example, if you had a web page accepting a list of items, and the user entered first sec,ond third. If you mash these together with , then your SQL code will end up interpreting this as four separate values first sec ond third.

For this and other performance and correctness reasons, it's probably better to just use a Table Valued Parameter.

user_148689970
Related