Log in  ::  Register  ::  Not logged in

## Single Quotation Marks in SQL

 Author Message sknox SSCertifiable Group: General Forum Members Points: 6994 Visits: 3161 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 SSCertifiable Group: General Forum Members Points: 5365 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! :-PMaybe you were confused like Bela Oxmyx... A Piece of the Action. Eirikur Eiriksson SSC Guru Group: General Forum Members Points: 94862 Visits: 20694 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 @@NESTLEVELSELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)PRINT @SQLSTREXEC 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 @SQLSTR2 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 SSCertifiable Group: General Forum Members Points: 5365 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 SSC-Addicted Group: General Forum Members Points: 495 Visits: 727 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 SSC Eights! Group: General Forum Members Points: 894 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 SSChampion Group: General Forum Members Points: 12477 Visits: 2121 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 FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------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 SSChampion Group: General Forum Members Points: 12477 Visits: 2121 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 @topsqlPRINT '-------'EXEC sp_executesql @topsql`The best test for the correct answer is of course to run it and see if the output works Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------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 SSCommitted Group: General Forum Members Points: 1774 Visits: 486 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 SSC Guru Group: General Forum Members Points: 129546 Visits: 22604 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.