Treatment of Special Characters

[Doug Baroter]

Hi,

This is a generic question, but for arguement's sake, let's say, my
environment is SQL Server 2000.

It seems that setting quoted_identifier off is the best way to
accomodate all sort of data input/update especially for data set that
contains special characters like single quote as in O'Brien, other
funky stuff like %^$*@#(!).
However, this option won't help with a situation like data value of
5'9",
and let's say, we really can't predicate if this type of value will be
used for ColA or ColB or ColC ...

Example
create table #tmp (col1 varchar(30));

set quoted_identifier off;
insert into #tmp values("O'Brien's barking dog'");
-- success
insert into #tmp values("O'book has funky (%^$*7) stuff");
-- success

insert into #tmp values("John said "funky is OK" in his speech");
-- failed

Globaly search and replace the double quotes before insert/update is
just too "expensive", not a good option.

Now, if we can set a custom quoted_identifier, we can solve the above
problem easily, for instance,

if MS SQL Server permits/accepts
set quoted_identier = '//$$';
then
insert into #tmp values(//$$John said "funky is OK" in his
speech//$$);
(or the like)
would succeed

Any other thoughts/ideas? Thanks.
Quote "Never stop thinking even though at times it may produce waste,
which we all do, btw :) "

[Erland Sommarskog]

Doug Baroter (qw********@boxfrog.com) writes:

It seems that setting quoted_identifier off is the best way to
accomodate all sort of data input/update especially for data set that
contains special characters like single quote as in O'Brien, other
funky stuff like %^$*@#(!).
Setting QUOTED_IDENTIFIER OFF may be a good idea when you are building
dynamic SQL strings and need to handle nested quote levels.

However, since QUOTED_IDENTIFIER must be ON for some features to be
available, this approach is not universally applicable.

Better is to use a function like the builtin quotename(). The only
drawback with quotename() is that it is restricted in input to 129 chars.
Then again, writing a scalar function that handles longer input is
no big deal.
Globaly search and replace the double quotes before insert/update is
just too "expensive", not a good option.

It's only the viable option. Dynamically building SQL statement rarely
involves reading a lot of rows. (Although the dynamic SQL may operate
on many rows.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Doug Baroter]

"Greg D. Moore \(Strider\)" <mo*****@greenms.com> wrote in message news:<7C*******************@twister.nyroc.rr.com>. ..

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

Globaly search and replace the double quotes before insert/update is
just too "expensive", not a good option.

Actually it IS the good option and the most common. It may not be, for instance, I have 28 columns for text data, and I
don't know what users are going to input/enter, the global search and
replace would have to be processed 28 times, I consider that
expensive, hence not a desirable option. As for "the most common" one
because there does not seem to be a better one yet, "most common"
seldom equates to the best or the most desirable.

Is my following suggestion pushing the envelope or you don't technical
need for it?

if MS SQL Server permits/accepts
set quoted_identier = '//$$';
then
insert into #tmp values(//$$John said "funky is OK" in his
speech//$$);
(or the like)
would succeed
I know everyting below. Thanks anyway.
but doubling single quotes.
i.e. whatever is passing your data to your SQL Server should take:

O'Reilly and make it O''Rielly (note, two SINGLE quotes, not a single double
quote) and then encapsulate that in single quotes.

So you get:

create table quotetest
(
teststring varchar(20)
)

insert into quotetest (teststring) values ('O''Reilly')

select * from quotetest

drop table quotetest

That will work.

Now, if we can set a custom quoted_identifier, we can solve the above
problem easily, for instance,

if MS SQL Server permits/accepts
set quoted_identier = '//$$';
then
insert into #tmp values(//$$John said "funky is OK" in his
speech//$$);
(or the like)
would succeed

Any other thoughts/ideas? Thanks.
Quote "Never stop thinking even though at times it may produce waste,
which we all do, btw :) "

[Doug Baroter]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

However, since QUOTED_IDENTIFIER must be ON for some features to be
available, this approach is not universally applicable. For instance?
Better is to use a function like the builtin quotename(). The only
drawback with quotename() is that it is restricted in input to 129 chars.
Then again, writing a scalar function that handles longer input is
no big deal.

How about execution efficiency if I have 28 to 45 columns of text data
where I don't know what data users may enter?

What do you think of the following scenario? justifiable or not?
Thanks.
Did "QUOTED_IDENTIFIER must be ON for some features to be available"
imply that this suggestion may not be able to accomodate/support
existing features?

if MS SQL Server permits/accepts
set quoted_identier = '//$$';
then
insert into #tmp values(//$$John said "funky is OK" in his
speech//$$);
(or the like)
would succeed

[Erland Sommarskog]

Doug Baroter (qw********@boxfrog.com) writes:

How about execution efficiency if I have 28 to 45 columns of text data
where I don't know what data users may enter?
In that case you can kill two birds with one stone. Write a stored
procedure to insert the values, and call the stored procedure with
the RPC mechanism. In this, you don't even have to bother. And your
code executes faster, because RPC is faster than calling stored
procedures by passing EXEC commands, which is faster by sending
INSERT statements.

(In ADO, you get RPC by setting commandtype to "adStoredProcedure", and
the you use the .Parameters collection to pass the parameters.
Did "QUOTED_IDENTIFIER must be ON for some features to be available"
imply that this suggestion may not be able to accomodate/support
existing features?

Probably. I have not penetrated exactly what go wrong with indexed
views and indexed computed columns if the restriction on QUOTED_IDENTFIER
was lifted.

In any case, the suggestion is useless, because whatever delimiter
you pick, the users can always use that delimiter in their input.
And this is not only an issue of usuability, but also about security.
By accepting user input without any processing, you open yourself to
SQL injection. For details see,
http://www.algonet.se/~sommar/dynami...html#Security2.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp