another changing code to prevent injection

  • rk1980factor

    SSCommitted

    Points: 1681

    How can I adjust the following Dynamic T-SQL script so as to prevent a SQL Injection in SQL Server: DECLARE @DynamicSQL [VARCHAR](1000) , @UserInput1 [VARCHAR](500) , @UserInput2 [VARCHAR](100) SET @DynamicSQL = ' UPDATE et SET ColA = ' + @UserInput2 + ' FROM ExampleTable et WHERE ColB = ' + @UserInput1 EXEC(@DynamicSQL)*

  • Grant Fritchey

    SSC Guru

    Points: 395316

    Same answer as on your other question. Parameterize the queries. Don't execute strings.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thom A

    SSC Guru

    Points: 98206

    Are these all interview/homework questions? ethical question, replication, Adjusting T-sql to prevent injection, accidenly dropping production, failover, idol steps to take.

    Why don't you tell us how you answered the question, and then we can tell you our opinions? Us giving you the answer here is clearly not the right thing to do here.

    Take the time to answer your own (your interview/homework question) and explain your answer; we'll be happy to then provide you with tips or advise where you might be going wrong.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 993862

    Thom A wrote:

    Are these all interview/homework questions? ethical question, replication, Adjusting T-sql to prevent injection, accidenly dropping production, failover, idol steps to take. Why don't you tell us how you answered the question, and then we can tell you our opinions? Us giving you the answer here is clearly not the right thing to do here. Take the time to answer your own (your interview/homework question) and explain your answer; we'll be happy to then provide you with tips or advise where you might be going wrong.

    Yeah... I agree... these all seem to be interview questions.  I've given the OP some clues on what to study for but good lord.  You can't teach ethics, proper communication, and proper protocols in a forum answer.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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