problems sending a string to execute in a stored procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need pass to a varchar param of a stored procedure a list with char values
separated with comma ( , ) example: '2','3','4'

then, at the procedure the query make a ... in ( @Param ) so in (
'2','3','4' )

the problem is the ado.net don´t permit passa quotation mark. No wonder, to
saffe sql injection. I should like to know if someone has a idea.

thanks a lot

my messenger is (e-mail address removed) if someone wants to take a
conversation with me.
 
Use the parameters collection of the SqlCommand object, like so:

Dim x As SqlCommand
Dim strTemp As String = "My Name"

x.CommandType = CommandType.StoredProcedure
x.CommandText = "sp_StoredProc"
x.Parameters.Add(New SqlParameter("@Name", strTemp))

Hope that helps.
 
EdwinSlyfingster said:
I need pass to a varchar param of a stored procedure a list with char values
separated with comma ( , ) example: '2','3','4'

then, at the procedure the query make a ... in ( @param ) so in (
'2','3','4' )

the problem is the ado.net don´t permit passa quotation mark. No wonder, to
saffe sql injection. I should like to know if someone has a idea.
Click to expand...

You can use Parameters collection as already said. Or the following
will probably work too: '''2'',''3'',''4''' -- you double every
single quotation mark inside the string and add one more at the beggining
and the end.

But this will not solve your problem. The following SQL code will not work:

declare @Param varchar(100)
set @Param = '''2'',''3'',''4''' -- this will do your string assignment
select ColList from Table where SomeCol in (@Param)

It is not going to work because IN clause expects a comma separated
list of actual values, while in your code you only have one varchar
value.
Here is a good article on the issue: http://www.sommarskog.se/arrays-in-sql.html
 
Back
Top