Hassle free parameters in dynamic sql.

Question

Post reply

Hassle free parameters in dynamic sql.

deepforest

SSCommitted

Points: 1780
More actions
April 24, 2014 at 3:25 pm

#292659

Comments posted to this topic are about the item Hassle free parameters in dynamic sql.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

arun.dendukuri Grasshopper Points: 17 More actions · Answer 1

I need some help

I have a table in SQL which has following columns

tablename columnname

aaaa cccc1

aaaa cccc2

aaaa cccc4

bbbb dddd1

bbbb dddd2

bbbb dddd3

dynamically I would like to build update statement and execute in T-SQL. I can build a script that update the table one column ata a time

can you suggest some logic that can generate script which can update all colunms in one single statement

similar to the below statement

update aaaa set cccc1 = '<some value>, cccc2 =<some value>, cccc3 = <some value>

Thanks

Arun

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

IMHO this is one way of generating a dynamic sql query but I have some doubts. It will certainly not produce reusable plans nor benefit from the usage of sp_executesql. It is also more of a hassle than simply write

DECLARE @SQL_STR NVARCHAR(MAX) = N'

SELECT

column1

,column2

,column3

FROM #t

WHERE column3 = @COL_VALUE';

DECLARE @SQL_PARAM NVARCHAR(MAX) = N'@COL_VALUE INT';

EXEC SP_EXECUTESQL @SQL_STR,@SQL_PARAM,6;

😎

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

This script may be ok to use on some occasions but if the entry of the parameters is from an external input, e.g. a web page, then it could be vulnerable to SQL injection - something that is not possible using parameters.

deepforest SSCommitted Points: 1780 More actions · Answer 4

Jonathan AC Roberts (5/15/2014)
...but if the entry of the parameters is from an external input, e.g. a web page, then it could be vulnerable to SQL injection - something that is not possible using parameters.

Thanks for the comment, but calling this from a web page, as you said, would be really scary...

Glen Cooper SSCommitted Points: 1927 More actions · Answer 5

This is a very clever way to table-drive a dynamic query.

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 6

This is clever but ultimately I would never use it.

It is wide open for SQL injection attack and it basically is a hello world type query in the really world I've never seen dynamic SQL this simple.

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

This explicitly facilitates SQL Injection, and second order SQL injection at that; it doesn't have to be a web page that does it.

Little Bobby Tables would also cause the students table to be dropped in this instance, because the parameter table would have had the SQL injection syntax that had previously been put into another table.

A better way is helping to learn how to use sp_executesql parameters - declare them, then set them. Personally, if I'm passing in @var, I call the parameter @parmvar.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

Interesting but probably won't use it. Thanks for the education though.

Sean Pearce SSCoach Points: 15999 More actions · Answer 9

insert @keywords select 'col1','''Robert''; DROP TABLE Students; --'

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

samot-dwarf SSC Eights! Points: 984 More actions · Answer 10

another point: the select @var = xxx from @tbl with multiple lines works, but is - as far I know - not official supported (= bad hack), so it could stop work sometimes (in another version)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 11

akljfhnlaflkj

SSC Guru

Points: 76202

February 25, 2016 at 4:16 pm

#1861266

Thanks for the script.