Parse string from C# to stored procedure

[Wing Siu]

Dear All Experts

I faced a problem which cannot solve

In C#, I am using SqlParameter to store the input parameter of stored
procedure with their value

Then in stored procedure, we have 2 style

First, run the SQL normal, e.g.

INSERT INTO Table1 (field1, field2, @param1, @param2)

Another is using EXECUTE sp_executesql, e.g.

SET @nvchSQL = N'INSERT INTO Table1(field1, field2, ' + @param1 + ', ' +
@param2 +')'

If the parameter contains single quote, the first one method is no problem,
but error will occur in method two
If I replace the single quote to two single quote, both can run correct, but
the field value in first method will contains two single quote

I dont want to replace the single quote in stored procedure since it is very
developer dependance, if the developer havent' replace the single quote, the
stored procedure maybe failed. And I would like to solve this problem in
programming level, said write a class to solve this problem, and all
developers will use that class no matter the stored procedure in first or
second format

Thanks

 

[Marc Gravell]

More a SQL point than a C# one, but if you are using sp_executesql you
should be using parameters *inside* the dynamic sql. Note that sp_executesql
accepts a number of parameters: the query (sql), the parameter declaration
(like the parameter list to an sp), and then the ordered values for each
parameter value.

E.g. (unchecked)
EXEC sp_excecutesql N'select @param1, @param2', N'@param1 int, @param2
varchar(20)', 5, 'test'

This then allows you to safely pass unescaped values into the dynamic sql
(put your sp params in place of the literals). It also allows re-use of the
query plan, without any danger of injection.

Marc

 

[Marc Gravell]

Better example; note that the parameter names don't have to match, but there
is no problem if they do - they are scoped separately. Here the "outer"
parameters would typically be your SP parameters.

DECLARE @outer_param1 int, @outer_param2 varchar(20)
SELECT @outer_param1 = 5, @outer_param2 = 'injection '' attempt'

EXEC sp_executesql
N'select @inner_param1, @inner_param2',
N'@inner_param1 int, @inner_param2 varchar(20)',
@outer_param1, @outer_param2

Marc

 

[Wing Siu]

wor~ thanks Marc~

Let me try this method~

thanks~

 

[Marc Gravell]

No problem