Dynamic SQL reading statements from table

#11

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... (AT) sommarskog (DOT) se> wrote:

Quote:

downwitch (downwi... (AT) gmail (DOT) 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... (AT) sommarskog (DOT) 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

#12

manstein (jkelly.admin (AT) gmail (DOT) com) writes:

Quote:

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.

Since char is fixed length, char(MAX) would imply a data type which is
always 2GB in size. I suspect that such a type would do more harm than
good.

In SQL 2005 you can use varchar(MAX), nvarchar(MAX) and varbinary (MAX).
These are the successors to text, ntext and image, which now are
deprecated. The MAX types works very much like the regular
(n)varchar/binary. In difference to the old types that had lots of
limitations.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Dynamic SQL reading statements from table

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Dynamic SQL reading statements from table - 05-02-2007 , 09:10 AM

Re: Dynamic SQL reading statements from table - 05-02-2007 , 10:04 AM