another changing code to prevent injection

Question

another changing code to prevent injection

rk1980factor

SSCommitted

Points: 1681
More actions
April 18, 2019 at 11:32 am

#3449184

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)*

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Thom A SSC Guru Points: 99133 More actions · Answer 2

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.
Larnu.uk

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)