467,077 Members | 1,002 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,077 developers. It's quick & easy.

Parse string from C# to stored procedure

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
Nov 7 '06 #1
  • viewed: 2496
Share:
4 Replies
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
Nov 7 '06 #2
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
Nov 7 '06 #3
wor~ thanks Marc~

Let me try this method~

thanks~

"Marc Gravell" <ma**********@gmail.comwrote in message
news:OO**************@TK2MSFTNGP03.phx.gbl...
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


Nov 8 '06 #4
No problem

Nov 8 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Steve Litvack | last post: by
1 post views Thread by Andrew Morton | last post: by
AdrianH
1 post views Thread by AdrianH | last post: by
15 posts views Thread by cj | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.