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?
CodePudding user response:
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.