January 4, 2013 at 10:20 am
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! 😛
January 4, 2013 at 10:36 am
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[/url]. :laugh:
January 6, 2013 at 6:33 am
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
January 6, 2013 at 2:22 pm
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!
January 7, 2013 at 11:46 pm
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.
January 13, 2013 at 3:16 pm
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).
January 13, 2013 at 4:10 pm
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/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 13, 2013 at 4:19 pm
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 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/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 11, 2014 at 1:18 am
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.
July 11, 2014 at 3:40 am
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 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2014 at 4:03 am
An Irish syndrome - introducing Mr and Mrs O'Brien 😎
Well, apostrophe was at least something you could predict and prevent - as soon as you knew it out there you could
easily overcome this challenge (write UDF once and do not forget to use it in ANY new database).
Try to deal with MULTIPLE character delimiters in a SINGLE character-delimited string - this is a real
challenge to understand you got into such troubles :alien:
July 11, 2014 at 5:16 am
I also go the same route, but I'm a bit lazy and typing @q is only 2 letters instead of the eight characters in CHAR(39).
declare @q varchar(1)
set @q = CHAR(39)
select 'O' + @q + 'Neil'
July 11, 2014 at 6:24 am
I am lazier: 😉
declare @q varchar(1) = CHAR(39)
July 11, 2014 at 6:51 am
declare var = ...
not valid in sql 2005 ...
July 11, 2014 at 7:18 am
peter.row (7/11/2014)
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.
You won't be able to parameterize everything so there is still a case for dynamic SQL. In fact I've always considered parameterized SQL of the type you are talking about to be a form of dynamic SQL. (Anything that uses sp_executesql or EXEC to run a query.) Also I frequently use dynamic SQL to generate scripts to run across multiple databases. No possibility of injection there since I'm the only one using the script.
As with any tool dynamic SQL can be used poorly and cause problems. That doesn't mean that all of us shouldn't learn how to use it, how to avoid problems and debug it when it does have problems. I've actually written about dynamic sql on my blog several times. You might find these interesting:
My best practice recommendations for dynamic SQL[/url]
Writing Dynamic SQL (A how to)[/url]
A generic dynamic SQL stored procedure[/url]
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/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 15 posts - 31 through 45 (of 73 total)
You must be logged in to reply to this topic. Login to reply