How to sanitise multiple value inputs for a parameter?

Hi there,

I have a report that I'm converting from Crystal onto MS SQL 2005, using SSRS. The report requires the users to enter a series of values, which are the cataloge numbers for thingies that they wish to report out. This is currently set as a mutli-value parameter. The values that are entered have no set format, sometimes they are a string of letters, sometimes a string of numbers, sometimes a bunch of numbers, a dash, then another bunch of numbers.

Do of you know of some code that I can use to sanitise the input for SQL injection attacks as well as seperating the multiple values into discrete inputs?

Forgive me if this isn't completely clear - it is late and I am tired.

more ▼

asked Feb 08, 2010 at 07:45 PM in Default

avatar image

33 3 2 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As long as you treat it as a parameter to a stored procedure and that stored procedure doesn't concatenate the value into s SQL string to execute, you should be protected from SQL injection. The trick is to agree on a separator character so you can use a function to split the string into a list that you can use in an IN clause. Here's a function that does that splitting:

create function [dbo].[fSplit] ( @List nvarchar(max), @SplitOn nvarchar(5) ) returns @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) as


Version : 1.0 Date : 31/08/2005


SELECT * FROM VenueChains WHERE VenueChainID IN (SELECT Value FROM dbo.fSplit('1,2,3,4',','))

So can use a stored proc to supply the option string '1,2,3,4'

*/ begin

 if @SplitOn = ' '
   SET @SplitOn = ','
   SET @List = REPLACE(@List, '  ',' ')
   SET @List = REPLACE(@List, ' ',',')

 While (Charindex(@SplitOn,@List)>0)

     Insert Into @RtnValue (value)
     Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

      Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

 -- now do the final left-over item
 Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List))




more ▼

answered Feb 09, 2010 at 05:09 AM

avatar image

David Wimbush
10.7k 31 34 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 08, 2010 at 07:45 PM

Seen: 2774 times

Last Updated: Apr 21, 2010 at 05:39 PM

Copyright 2018 Redgate Software. Privacy Policy