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 4 2663
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Steve Litvack |
last post by:
Hello,
I have built an XMLDocument object instance and I get the following string
when I examine the InnerXml property:
<?xml version=\"1.0\"?><ROOT><UserData UserID=\"2282\"><Tag1...
|
by: Andrew Morton |
last post by:
I've got a stored procedure which works as expected using Run Stored
Procedure in VS.NET:-
-----------------------------------------------------------
ALTER PROCEDURE dbo.ValidateUser
@Username...
|
by: Bruno BAGUETTE |
last post by:
Hello,
I have a PL/PGSQL stored procedure that makes me mad currently... (The
stored procedure is a procedure that simulates a materialized view)
It complains about a parse error when I call...
|
by: Assimalyst |
last post by:
Hi,
I have two methods. In one i would like to create a string from a
stored procedure to pass to the second which populates a datagrid.
private void method()
{
string sqlString =...
|
by: Roger |
last post by:
I have a stored procedure running on DB2 V7 Z/os calling a COBOL
program to do some inserts. The stored procedure have 3 input columns
and one column is of varchar(32648)
The stored procedure is...
|
by: Roger |
last post by:
have a stored procedure running on DB2 V7 Z/os calling a COBOL
program to do some inserts. The stored procedure have 3 input columns
and one column is of varchar(32648)
The stored procedure is...
|
by: AdrianH |
last post by:
Assumptions
I am assuming that you know or are capable of looking up the functions I am to describe here and have some remedial understanding of C programming.
FYI
Although I have called this...
|
by: bobc |
last post by:
In my stored procedure, I want to parse @ArrayOfDays into @d1 through
@d5.
@ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.
Most active...
|
by: cj |
last post by:
I've got a long string to be written to a field in a sql db table via
stored procedure. For some reason I find only 258 chars are being
written. Any ideas?
relevant bits of my VB2005 code:
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |