Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single Quotation Marks in SQL


Single Quotation Marks in SQL

Author
Message
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2712
Steven Willis (1/4/2013)


For anyone who really cares about proper typography The Chicago Manual of Style is the editor's Bible.

 


Oh. I thought the Chicago Manual of Style was about pinstripes and fedoras. Thanks for clearing that up! :-P
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1721
sknox (1/4/2013)
Steven Willis (1/4/2013)


For anyone who really cares about proper typography The Chicago Manual of Style is the editor's Bible.

 


Oh. I thought the Chicago Manual of Style was about pinstripes and fedoras. Thanks for clearing that up! :-P

Maybe you were confused like Bela Oxmyx... A Piece of the Action. Laugh

 
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6743 Visits: 17721
Nice article and a classic subject.
One workaround is to use tokens when writing dynamic SQL statements, especially if nested more than one level;


DECLARE @TOKEN NVARCHAR(1) = NCHAR(123)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'
DECLARE @TOKEN NVARCHAR(1) = NCHAR(124)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N{

DECLARE @TOKEN NVARCHAR(1) = NCHAR(125)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N|

DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N}

DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
}
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR

|
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR

{
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR




Execution result;

0

DECLARE @TOKEN NVARCHAR(1) = NCHAR(124)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'

DECLARE @TOKEN NVARCHAR(1) = NCHAR(125)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N|

DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N}

DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
}
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR

|
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR

'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
2


DECLARE @TOKEN NVARCHAR(1) = NCHAR(125)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'

DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N}

DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
}
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR

'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR


4


DECLARE @TOKEN NVARCHAR(1) = NCHAR(126)
DECLARE @SQ NVARCHAR(1) = NCHAR(39)
DECLARE @SQLSTR NVARCHAR(MAX) = N'

DECLARE @quotedvar nvarchar(100) = N~O¡Neil~
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar
'
PRINT @@NESTLEVEL
SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)
PRINT @SQLSTR
EXEC sp_executesql @SQLSTR


6


DECLARE @quotedvar nvarchar(100) = N'O¡Neil'
SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39))
PRINT @quotedvar

O'Neil



Eirikur
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1721
Eirikur Eiriksson (1/6/2013)
One workaround is to use tokens when writing dynamic SQL statements, especially if nested more than one level;

That sounds like a great idea. I've never seen the problem dealt with like that before. Thumbs up!

 
Dave Vroman
Dave Vroman
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 716
Since our apps are primarily web pages we step around the problem by converting the problem characters to unicode. The single quote character is converted to "'". It no longer is in the way, there is no special coding necessary and it displays properly in a browser.
fregatepllada
fregatepllada
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 322
IMHO dynamic SQL outside of database is a recepie for a disaster.
Anybody hear about SQL injection in last 15+ years?
Why not use parameterised stored procedures/user-defined functions.

I would use dynamic SQL only for Sql script generation or
Within stored procedure (in exceptional cases when nothing
Else could work).
Kenneth.Fisher
Kenneth.Fisher
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3592 Visits: 2021
I would tend to agree. Dynamic SQL is a very powerful tool. But it's just one tool of many and should only be used when it's appropriate and with appropriate attention paid to security. For that matter combining parameters and dynamic sql can be particularly powerful.

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Kenneth.Fisher
Kenneth.Fisher
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3592 Visits: 2021
Sorry I'm a little bit late posting my answer to the "homework". Lot's of people had posted correct answers already so I didn't feel all that rushed to post my answer :-).

DECLARE @topsql nvarchar(200)

SET @topsql =
'DECLARE @quotedvar nvarchar(100) ' + char(13) +
'DECLARE @sql nvarchar(1000) ' + char(13) +
'' + char(13) +
'SET @quotedvar = ''O''''Neil''' + char(13) +
'' + char(13) +
'SET @sql = ''PRINT '''''' + REPLACE(@quotedvar,'''''''','''''''''''') + ''''''''' + char(13) +
'' + char(13) +
'PRINT @sql' + char(13) +
'' + char(13) +
'EXEC sp_executesql @sql'

PRINT @topsql
PRINT '-------'

EXEC sp_executesql @topsql




The best test for the correct answer is of course to run it and see if the output works Smile

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
peter.row
peter.row
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 395
I know the article is this simple to highlight the issue but I assert in real life you would not do this.
If you are doing dynamic SQL then unless you want to be laughed at for introducing SQL injection into your work you are going to always uses parameterised SQL in which case quoting is not a problem in the exact same way it would not be a problem using a parameter that wasn't dynamic SQL.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8365 Visits: 19499
Apologies - I have not read all of the thread, so this may already have been covered.

It may be of interest to note that, although they are often confused, a single quote is technically different from an apostrophe.

Apostrophe: ' (Alt-0039)

Opening Single quote: ‘ (Alt-0145)
Closing single quote: ’ (Alt-0146)

Yes, I am great fun to talk to at a party :-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search