I was asked to get rid of this Dynamic SQL for performance purposes.

Question

Post reply

I was asked to get rid of this Dynamic SQL for performance purposes.

VoldemarG

Hall of Fame

Points: 3852
More actions
March 19, 2020 at 10:26 pm

#3735725

Is it REALLY possible to bypass the @Where_Clause variable and make the below SQL non-dynamic?

This is a statement from an SP that receives the below 5 parameters and dynamically builds the WHERE clause from them.

I don't think it is possible NOT TO USE DYNAMIC SQL HERE, but double-checking with you guys.

Thanks.

-- Concatenate all Where Clauses Together (all 5 are INPUT PARAMETERS into this SP,

-- and are different every time the SP is called)

SELECT @Where_Clause = @Where_Clause1 +

@Where_Clause2 +

@Where_Clause3 +

@Where_Clause4 +

@Where_Clause5;

declare @vsql varchar (max)

SET @vsql =

'SELECT DISTINCT

EC.EC_id,

EC.p_id,

FROM E_Contacts EC

JOIN Contacts C

ON C.Contact_ID = EC.EC_id

WHERE ' + @Where_Clause +

' AND EC.EC_Class = ''M'''

INSERT INTO #EC_IDS

EXEC(@vSQL);

Likes to play Chess

Viewing 7 posts - 1 through 6 (of 6 total)

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

jonathan.crawford SSCertifiable Points: 6604 More actions · Answer 1

not without more info. if there are 5 input parameters, do they all have "valid" lists of values? if so, then there should be a precondition within the stored procedure to check that the input value exists in the list of valid values, and if not, then it's not combined in a dynamic SQL string and executed. (error should be returned letting the user know, and will probably have to bubble back up to the report they run it from)

https://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/

and for more fun: https://bertwagner.com/2018/03/20/how-to-steal-data-using-a-second-order-sql-injection-attack/

for some design ideas:

https://qa.sqlservercentral.com/articles/how-to-design-build-and-test-a-dynamic-search-stored-procedure

Now while Dwain's article on a search procedure is great, what he did NOT do is 1) use a try/catch structure or 2) validate the inputs against a list first.

Hope that helps you, that's maybe not an easy overhaul but will be much less subject to attack.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

ScottPletcher

SSC Guru

Points: 100262

March 20, 2020 at 2:36 pm

#3735866

You're right, you can't avoid dynamic SQL here. You need dynamic SQL here to insure you get the best query plan.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

I am going to say that it depends. To provide a better answer we would need to see the actual procedure, see the possible values being passed in to the procedure, would also need to know if all the parameters are required or if some could be empty meaning that they should be ignored.

I will be honest, I have rewritten code where developers wrote dynamic SQL where it wasn't needed once you took a closer look at the code.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

the only thing I can bring to the party here is that you are open for SQL injection attacks

if you have to use dynamic SQL in that way, is there any way you can ensure you inputs are sanitised?

it looks like I could do terrible things with basic sql injection attack tecniques

just think where 1=1 will give you all rows

then stick a ; a few -- and some mean bits of code and you are in trouble

MVDBA

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

If you're going to make dynamic SQL for the WHERE clause, there's a right way to do it and using EXEC() is NOT the right way. Please see the following article on "Catch All Queries" by none other than Gail Shaw. I consider this article and the one she references to be the definitive articles on the subject. The method can be both awesome for performance and, if you follow her instructions, are totally safe from SQL Injection.

https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--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)
Intro to Tally Tables and Functions

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 6

VoldemarG wrote:

Is it REALLY possible to bypass the @Where_Clause variable and make the below SQL non-dynamic?

This is a statement from an SP that receives the below 5 parameters and dynamically builds the WHERE clause from them.

I don't think it is possible NOT TO USE DYNAMIC SQL HERE, but double-checking with you guys.

You haven't put any details of what possibilities can go in the WHERE clause. So I can't say whether or not you can make it non-dynamic.