Problem with single quotes in SQL statement

E

Elmo

Hi all!

I am not very proud to ask this but here is my problem:

string code = "\'13\'"

The string code will have to contain following info:
'51','52','63','other'...
to get certain info from the database. When the querry is parsed these
values will be looked up:
....
compCode in (@code)
....

Can someone tell me why these values are not found in the DB? I figured
out that maybe he puts his own quotes so I tried the following:

"51" -->works (but only for one value?)
" \' 51\' " --> does not work
"'51'" --> does not work

Does anyone have some ideas how I can look up the right values? THX!

DateTime startDate = new DateTime(2006,12,14,0,0,0);
DateTime endDate = new DateTime(2006,12,15,23,59,59);
string campaignCode = "O850";
string code = "\'13\'"; //,\'51\'
string language = "NL";
string startdate = Convert.ToString(startDate.Year + "-" +
startDate.Month + "-" + startDate.Day);
string enddate = Convert.ToString(endDate.Year + "-" +
endDate.Month + "-" + endDate.Day);
// define connection
sqlConn = new SqlConnection(CONNECTION);

// define query

sqlQuery = "select count(*) as Aantal, sum(talktime) as TalkTime, "
+ "sum(updatetime) as UpdateTime, sum(talktime + updatetime) as
HandleTime "
+ "from [REPORTSERVERRPT].dbo.SOMETHING"
+ "where calldate > @startdate and"
+ " calldate < @enddate and "
+ "compCode in (@code) and "
+ "sleutel in ("
+ "select up.sleutel "
+ "from [REPORTSERVER-RPT].dbo.TEST as up "
+ "where up.CampaignName = @campaign and "
+ "up.lang = @language)";
 
E

Elmo

That is absolutly not the problem (but thx though ;-))
Maybe following output from sql profiler will help you guys :

Values of my parameters:
INPUT IS :

string code = "13" + "'" + "," +"'" + " 51";

OUPUT IS :
@startDate = '2006-12-14', @endDate = '2006-12-15', @campaign = 'O850',
@language = 'NL', @code = '13'','' 51'

So without asking he puts an extra quote in the string for each single
quote i use

Mark Rae schreef:
 
P

Peter Bradley

Ah! I see what you're trying to do. You're trying to do a macro
substitution. You can't do that.

The IN operator is expecting a number of comma delimited parameters (sort
of). You're passing one parameter to it, so it treats it as the first item
in the list - not as a list of many items. I haven't explained that too
well; but I hope you see what I'm getting at.

I'm afraid you'll just have to type in the individual values.

Of course, if you can retrieve the values from a table, you can do a
subselect inside the IN operator's brackets; which could save you some
typing, I suppose.

HTH


Peter
 
M

Mark Rae

So without asking he puts an extra quote in the string for each single
quote i use

Ah right - yes, that is standard ADO.NET behaviour for string parameters
because a single quote in T-SQL is the string variable delimter. When T-SQL
encounters a single quote, it interprets it as the beginning of a string
variable containing everything which follows until the next single quote is
reached. To get round the obvious problem of what to do with a string
variable need to contain a single quote, one single quote is converted into
two single quotes.
 
E

Elmo

Peter,

Yup, you understand the problems I'm facing... I also thought of this
extra table you described, but after carefull consideration we
(programmers team) decided against that because these values will vary
(depending on the campaign).

Quote -

so it treats it as the first item
in the list - not as a list of many items. I haven't explained that too
well; but I hope you see what I'm getting at.

I'm afraid you'll just have to type in the individual values.

/Quote

Typing the individual values is the best option but I can't allow users
to change sql statements... and we explicitly decided AGAINST dynamic
SQL :(. I also considered parsing every code (51,60,...) individually
but that is not gonna work because null values can occur (not to
mention the performance issues).
 
M

Mark Rae

Yup, you understand the problems I'm facing... I also thought of this
extra table you described, but after carefull consideration we
(programmers team) decided against that because these values will vary
(depending on the campaign).

Quote -

so it treats it as the first item

/Quote

Typing the individual values is the best option but I can't allow users
to change sql statements... and we explicitly decided AGAINST dynamic
SQL :(. I also considered parsing every code (51,60,...) individually
but that is not gonna work because null values can occur (not to
mention the performance issues).

Apologies - I now understand your problem too...

You can get round the problem of creating dynamic SQL client-side, but
you'll have to use a stored procedure instead and create dynamic SQL in
that. Here's an example from AdventureWorks...

CREATE PROCEDURE Person.TestProc
@pstrCriteria varchar(100) = NULL
AS

DECLARE @strSQL nvarchar(4000)

SET @strSQL =
'
SELECT
*
FROM
Person.CountryRegion
WHERE
CountryRegionCode IN (' + REPLACE(@pstrCriteria, '¬', '''') + ')'
EXEC sp_executesql @strSQL

Then, run the following SQL:
Person.TestProc '¬AD¬,¬AE¬,¬AF¬'

The trick is to use a very uncommon character (¬, in this case) to pretend
to be the text delimiter...

I fully appreciate that this looks like a complete hacky kludge - and it
is! - but it works, it's extremely fast, it gets round the problem of
client-side SQL Injection, and I haven't found a neater way so far... :)
 
M

Mark Rae

''51'',''52'',''63'',''other''...

(doubled single quotes - Transact-SQL considers them escaped)
should work.

Should, but shouldn't... at least, not in the context that the OP wishes to
use them...
 
G

gineshkumar

Try this...

SET @strCriteria = '51,52,63,other' --> input string
--------------------------------
Procedure

if (Left(@strCriteria,1) <> ',')
set @strCriteria = ',' + @strCriteria

if (Right(@strCriteria,1 ) <> ',')
set @strCriteria = @strCriteria + ','


SELECT
*
FROM
Person.CountryRegion
WHERE
charindex( ','+ CountryRegionCode + ',',@strCriteria) <> 0


EXEC sp_executesql @strSQL
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top