Posted by downwitch on 05/02/07 14:10

Perhaps I wasn't as clear as I should have been. Regardless, the
problem is solved--turns out it wasn't just "related" to the first
problem, it *was* the first problem. That's why I'd introduced the
quotestring function, actually, because when I switched from variable
to SQL (on a more complicated query, obviously, than the example I
provided, including multiple parameter values), the string failed
without doubling its parameter quotes. And then I was seeing the
quotes around the SQL string as an output result, not a part of the
string...

In short, duh on me.

RE the security risk, I'm fully aware of it. But as is often the case
with a very sensitive db, if anyone even gains access to it in the
first place there are much bigger potential headaches than whether or
not they want to drop a nasty dynamic SQL statement on it.


On May 1, 6:13 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> downwitch (downwi...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]