Hassle free parameters in dynamic sql.

  • deepforest

    SSCommitted

    Points: 1780

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

  • arun.dendukuri

    Grasshopper

    Points: 17

    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: 182344

    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: 16734

    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

    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

    Default port

    Points: 1491

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

    R Glen Cooper

  • peter.row

    SSCarpal Tunnel

    Points: 4295

    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

    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

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

  • Sean Pearce

    SSCoach

    Points: 15750

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

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • samot-dwarf

    SSC Eights!

    Points: 984

    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

    Thanks for the script.

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

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