Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kenneth Fisher » Single Quotation Marks in SQL
Add to briefcase
38 posts, Page 4 of 4
«««1234

Single Quotation Marks in SQL Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sknox
Posted Friday, January 04, 2013 10:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:22 AM
Points: 1,037, Visits: 1,354
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!
Post #1402993
Steven Willis
Posted Friday, January 04, 2013 10:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 283, Visits: 1,237
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!

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

 
Post #1403004
Eirikur Eiriksson
Posted Sunday, January 06, 2013 6:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:23 PM
Points: 17, Visits: 146
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
Post #1403328
Steven Willis
Posted Sunday, January 06, 2013 2:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 283, Visits: 1,237
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!

 
Post #1403355
Dave Vroman
Posted Monday, January 07, 2013 11:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:13 AM
Points: 46, Visits: 204
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.
Post #1403986
Irozenberg 1347
Posted Sunday, January 13, 2013 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 7:16 AM
Points: 7, Visits: 43
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).
Post #1406517
Kenneth.Fisher
Posted Sunday, January 13, 2013 4:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563
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
Post #1406526
Kenneth.Fisher
Posted Sunday, January 13, 2013 4:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563
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 :)

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
Post #1406528
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 4 of 4
«««1234

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.