Formatting NVARCHAR with xp_sprintf and dynamic list of parameters

[nicolasclaudia]

Hi all I need your help, how can you format a string with placeholders with the number of placeholders not known in advance (dynamic number of placeholders) ?

I need to do that in T-SQL

"xxxxx {0} yyyy {1} zzzz {2} "

with {} replaced by parameters.

I did the code:

DECLARE @PARAMETERS nvarchar(500)

DECLARE @TEST nvarchar(500)

DECLARE @QUERY  nvarchar(500)

SET @TEST = '%s | %s | %s'

SET @PARAMETERS='a,b,c'


SET @QUERY = '

PRINT @TEST;
PRINT @PARAMETERS 


EXEC xp_sprintf @TEST out, @TEST,' + @PARAMETERS + '

PRINT @TEST'


EXECUTE sp_executesql @QUERY,N'@TEST nvarchar(500), @PARAMETERS nvarchar(500)', @TEST,@PARAMETERS

with the results:

%s | %s | %s
a,b,c
a | b | c

but can I avoid the concatenation ' + @PARAMETERS + ' ?
If I don't use concatenation I get the error :

Error executing extended stored procedure: Invalid Parameter
Msg 50003, Level 1, State 0


Thanks :)

[Rabbit]

I don't understand, if it works, why would you want to change it?

You should be aware that your procedure is open to SQL injection attacks so you should escape your inputs.

[nicolasclaudia]

Hello Rabbit, thanks for your input :) I need to change it so as to avoid SQL injection like you correctly mentioned, can you please tell me how to escape my inputs to avoid it? Or do you have a solution to avoid the risk in the first place?

By the way my inputs are actually results from a "SELECT" in my stored procedure from another table which is static. So can you make sure the data there are escaped in T-SQL?

I'd be very grateful for your valued feedback :) Thanks again ;-)

[NeoPa]

We don't want this thread diverted onto a discussion of that, even though it is a very important issue in its own right. Please read SQL Injection Attack for more on the subject, but as the threat is from humans, static data would make this irrelevant in this situation.

If you're still curious then please feel free to post a separate question on the matter.

[deepuv04]

hi below is the script to replace number with placeholders.

DECLARE @Str VARCHAR(100),
		@NewString varchar(100)

SET @Str = 'xxxxx 0 yyyy 1 zzzz 2'

DECLARE @I INT,
		@start int,
		@end int,
		@initial int
SELECT @I = 0, @start = 1, @initial = 1

WHILE @I <= LEN(@STR)
BEGIN

	WHILE isnumeric(substring(@str,@start, 1)) = 0 AND @start <= LEN(@STR)
	BEGIN
		SET @start = @start + 1
		PRINT @START
	END
	
	set @end = @start

	WHILE isnumeric(substring(@str,@end, 1)) = 1 AND @end <= LEN(@STR)
	BEGIN
		SET @end = @end + 1
		PRINT @end
	END
		
	SELECT @NewString = isnull(@NewString,'') + Substring(@str, @initial,@start - @initial)  + 
						case when @start = @end then '' else '{' + Substring(@str, @start,@end - @start) + '}' end
	
	SELECT @i = @end+1, @start = @end+1, @initial = @end
	
END
--END

SELECT @STR,@NewString