Adjusting T-sql to prevent injection

Question

Adjusting T-sql to prevent injection

rk1980factor

SSCommitted

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

#3448825

How can I adjust the following Dynamic T-SQL script so as to prevent a SQL Injection in MySQL: SET @DynamicSQL = ' UPDATE et SET ColA = ? FROM ExampleTable et WHERE ColB = ?' SET @a = 'ValA' SET @b-2 = 'ValB' EXECUTE @DynamicSQL USING @a, @b-2;*

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

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

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

Switch all your dynamic T-SQL to use sp_executesql. As you can see in the documentation, you can write that such that it uses parameter values, not just constructed strings. The use of parameterized queries is the single best approach to eliminating any issues with SQL Injection. Executing strings, without parameters, is how you get SQL Injection. Don't do that.

"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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

MySQL and SQL Server use different methods to execute dynamic code. This is a forum for Microsoft SQL Server, you would get better help on a MySQL forum.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Luis Cazares wrote:

MySQL and SQL Server use different methods to execute dynamic code. This is a forum for Microsoft SQL Server, you would get better help on a MySQL forum.

Oh bugger. I didn't even spot that. Thanks!

There is no MySQL equivalent to sp_executesql, so that's not an option. Instead, make your code into prepared statements. Same core solution, parameterize the query.

"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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

This guy has asked a half dozen interview questions. I'm not helping him to get a job he's not qualified to do.

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