Posted by Erland Sommarskog on 05/01/07 22:13

downwitch (downwitch@gmail.com) writes:
> Here's my version of the function:
> -----------
> CREATE FUNCTION uQuoteString(@str nvarchar(MAX)) RETURNS nvarchar(MAX)
> AS
> BEGIN
> DECLARE @ret nvarchar(MAX),
> @sq nvarchar(4)
> SELECT @sq = ''''
> SELECT @ret = replace(@str, @sq, @sq + @sq)
> RETURN(@sq + @ret + @sq)
> END
> -----------
>
> So running
>
> DECLARE @sql nvarchar(MAX)
> SET @sql ='SELECT foo FROM foostable'
> SET @sql = dbo.uQuoteString(@sql)
> EXEC sp_executesql @sql
>
> I now get: Incorrect syntax near 'SELECT foo FROM foostable'
>
> Note that the error has changed, no longer referencing the stored proc
> but instead the @sql argument.

I added a PRINT @sql to your SQL batch, and this is what I saw:

'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.

A string on its own is not legal T-SQL.

I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.

Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]