CODEWITHSUNDEEP
sqlt-sqlstored-proceduresdynamicsql-server-2000
Mr.J
Mr.J

Reputation: 440

sp_execute SQL vs exec

as i have read in the following blog...

http://www.sommarskog.se/dynamic_sql.html

dynamic sql is unsafe, that's why we have sp_execute SQL.

could i ask, is there any way to make sp_execute run a procedure like this...

declare @QueryMain as varchar(8000)
declare @QueryBody as varchar(8000)
declare @QueryWhere as varchar(8000)

set @QueryMain = <8000 charactes>
set @QueryBody = <8000 characters>
set @QueryWhere = <8000 characters>

exec (@queryMain+@QueryBody+@QueryWhere)

this would run, but as i have read in the article, it is not recommended... but i would like to know if there is another way to run this kind of query... i tried to incorporate the 3 variables to ntext but it didnt worked, and btw... the example query above would have other variables inside there query which could be dates, userIDs, anything!

i am using sql server 2000 btw... and as i have googled, i found no results how to manipulate large strings for sql server 2000, but i have this consequence of the SQLi attack.

Upvotes: 0

Views: 8215

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

If you're asking if you can do this:

EXEC sp_executesql @QueryMain + @QueryBody + @QueryWhere;

No, you cannot have expressions or formulas as parameter values. But why do you need to? The workaround is simple, just append them in an intermediate variable (and this should be NVARCHAR(MAX), not NTEXT which is both deprecated and isn't valid for local variables):

DECLARE @sql NVARCHAR(MAX);

...

SET @sql = @QueryMain + @QueryBody + @QueryWhere;

EXEC sp_executesql @sql;

In fact all of these variables should be NVARCHAR.

See this post for some more on EXEC vs. sp_executesql.

Upvotes: 3

Related Questions