Building dynamic sql query with deafult characters

[Guest]

Hi All,

What is the good way to handle a string which contains characters like
single quote (') or square bracket with LIKE ([]), while building a SQL
statement. Right now iam searching these characters in the string and adding
the escape chars for that.

eg 1: (This will fail to search)

String* searchVal = S"Test'quote";
String* str = S"";
str = String::Format(S"Select * from t1 where c1 = '{0}'", searchVal);

eg 2: (so i need to add a extra ' in the string)
String* searchVal = S"Test''quote";

Any other solutions for this

Hari

 

[Frans Bouma [C# MVP]]

Hi All,

What is the good way to handle a string which contains characters
like single quote (') or square bracket with LIKE ([]), while
building a SQL statement. Right now iam searching these characters in
the string and adding the escape chars for that.

eg 1: (This will fail to search)

String* searchVal = S"Test'quote";
String* str = S"";
str = String::Format(S"Select * from t1 where c1 = '{0}'",
searchVal);

eg 2: (so i need to add a extra ' in the string)
String* searchVal = S"Test''quote";

Any other solutions for this

never concat values into a query. Use a parameter like this:
str = S"SELECT * FROM t1 WHERE C1=@param";

and then create a new parameter:
SqlParameter* param = new SqlParameter();
// fill in parameter's properties here.
// add it to the SqlCommand object:
cmd.Parameters.Add(param);

FB


--

 

[Guest]

Thanks Frans, Also Iam is works if use 'LIKE' within a where clause? (Iam
not sure about using wild characters within a param object???)
eg.

SELECT * from t1 WHERE c1 LIKE @param

Hari

Hi All,

What is the good way to handle a string which contains characters
like single quote (') or square bracket with LIKE ([]), while
building a SQL statement. Right now iam searching these characters in
the string and adding the escape chars for that.

eg 1: (This will fail to search)

String* searchVal = S"Test'quote";
String* str = S"";
str = String::Format(S"Select * from t1 where c1 = '{0}'",
searchVal);

eg 2: (so i need to add a extra ' in the string)
String* searchVal = S"Test''quote";

Any other solutions for this

never concat values into a query. Use a parameter like this:
str = S"SELECT * FROM t1 WHERE C1=@param";

and then create a new parameter:
SqlParameter* param = new SqlParameter();
// fill in parameter's properties here.
// add it to the SqlCommand object:
cmd.Parameters.Add(param);

FB


--

 

[Guest]

Hari,

One way:

cmd.CommandText = "Select * From Students Where Name Like @Name"
cmd.Parameters.Add("@Name", "%John%")

Kerry Moorman

Thanks Frans, Also Iam is works if use 'LIKE' within a where clause? (Iam
not sure about using wild characters within a param object???)
eg.

SELECT * from t1 WHERE c1 LIKE @param

Hari

Hi All,

What is the good way to handle a string which contains characters
like single quote (') or square bracket with LIKE ([]), while
building a SQL statement. Right now iam searching these characters in
the string and adding the escape chars for that.

eg 1: (This will fail to search)

String* searchVal = S"Test'quote";
String* str = S"";
str = String::Format(S"Select * from t1 where c1 = '{0}'",
searchVal);

eg 2: (so i need to add a extra ' in the string)
String* searchVal = S"Test''quote";

Any other solutions for this

never concat values into a query. Use a parameter like this:
str = S"SELECT * FROM t1 WHERE C1=@param";

and then create a new parameter:
SqlParameter* param = new SqlParameter();
// fill in parameter's properties here.
// add it to the SqlCommand object:
cmd.Parameters.Add(param);

FB


--

 

[Alec MacLean]

Thanks Frans, Also Iam is works if use 'LIKE' within a where clause? (Iam
not sure about using wild characters within a param object???)
eg.

SELECT * from t1 WHERE c1 LIKE @param

Hari

Even better, don't build query as a string at all and use a stored procedure
(assuming SQL Server of some flavour). The use of the parameters and stored
procedures is the recommended approach by MS to prevent risk of your app
being attacked by the SQL injection technique.

Assuming your searched column is a varchar or text datatype, in the stored
procedure, append the wildcard symbol like this:

create procedure dbo.usp_Wildcard_SELECT
@SearchValue VARCHAR(30)
AS

SELECT Col1, <Col2 , etc as required...>
FROM yourSourceTable
WHERE
Col1 LIKE @SearchValue + '%'
-- optional ordering output
ORDER BY Col1


The above example will always add the wildcard to the end of whatever string
value is passed in. This is a common approach for finding records by using
peoples surnames for example.


Al