Building dynamic sql query with deafult characters

9 Feb 2006 7:04 PM

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

Hari

10 Feb 2006 8:44 AM

Frans Bouma [C# MVP]

Hari wrote:

Show quote

> 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

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

10 Feb 2006 6:34 PM

Hari

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

Show quote

"Frans Bouma [C# MVP]" wrote:

> Hari wrote:
>
> > 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
>
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------
>

10 Feb 2006 7:24 PM

Kerry Moorman

Hari,

One way:

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

Kerry Moorman

Show quote

"Hari" wrote:

> 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
>
>
> "Frans Bouma [C# MVP]" wrote:
>
> > Hari wrote:
> >
> > > 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
> >
> >
> > --
> > ------------------------------------------------------------------------
> > Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> > My .NET blog: http://weblogs.asp.net/fbouma
> > Microsoft MVP (C#)
> > ------------------------------------------------------------------------
> >

12 Feb 2006 11:20 AM

Alec MacLean

Show quote

"Hari" <H***@discussions.microsoft.com> wrote in message
news:ED8DA845-592D-4CF0-8B6D-0ED0D18BB638@microsoft.com...
> 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

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

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