Is there a way to identify dynamic sql that may be vulnerable to sql injection?

Question

Is there a way to identify dynamic sql that may be vulnerable to sql injection?

Viewing 11 posts - 16 through 25 (of 25 total)

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

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 1

The bottom line for all of the recommendations is that although you can easily find all of the places where dynamic SQL has been used, it's going to take a human to figure out if the dynamic SQL is susceptible to an injection or other attack because there are a ton of different methods to make a mistake when using dynamic SQL including concatenation and simple "token replacement" of variables and strings from tables and there are many ways of doing either of those. I don't know of anything written that would certainly and correctly identify and report all of the different possibilities.

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

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

HappyGeek - Wednesday, January 3, 2018 7:34 AM
juniorDBA13 - Wednesday, January 3, 2018 7:05 AM
I understand all of this.
My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem
Information_schema.routines view can be queried to give you definitions for procedures and functions but not UDF's. Look this up if you are not sure.

UDFs are not open to SQL injection. Either way, they're all stored in sys.sql_modules.

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: 398694 More actions · Answer 3

juniorDBA13 - Wednesday, January 3, 2018 7:05 AM
I understand all of this.
My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem

You can monitor your server using Extended Events to capture the queries, both rpc_completed and sql_batch_completed. You can pretty quickly begin to assess if the queries coming in are likely to be problematic (and if it's dynamic T-SQL without parameters, it is problematic, period).

"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: 1003870 More actions · Answer 4

grant@scarydba.com - Wednesday, January 3, 2018 8:53 AM
juniorDBA13 - Wednesday, January 3, 2018 7:05 AM
I understand all of this.
My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem
You can monitor your server using Extended Events to capture the queries, both rpc_completed and sql_batch_completed. You can pretty quickly begin to assess if the queries coming in are likely to be problematic (and if it's dynamic T-SQL without parameters, it is problematic, period).

How do you distinguish what's dynamic SQL v.s. non-dynamic SQL using Extended Events?

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sp_statement_completed & sql_statement_completed and look for sp_executesql or EXEC with brackets.
Or the entire batch with sql_batch_completed and RPC completed. Eitherway, a second filter is necessary, and probably better to do it after capturing, especially for the EXEC( one

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thom A SSC Guru Points: 99126 More actions · Answer 6

GilaMonster - Wednesday, January 3, 2018 11:21 AM
sp_statement_completed & sql_statement_completed and look for sp_executesql or EXEC with brackets.
Or the entire batch with sql_batch_completed and RPC completed. Eitherway, a second filter is necessary, and probably better to do it after capturing, especially for the EXEC( one

That would work for SQL generated on the SQL server, but isn't going to help with dynamic SQL generated on an application. From experience, I've found applications are far worse for it, it seems that (some) application/web developers don't even consider; and then as a DBA you see the application code and have a melt down. :'(

Unfortunately, I can't really think of a way you could capture dynamically generated SQL from an application. It is, one of the reasons why I prefer only letting an application use SPs; as then it can't even try to use dynamically generated SQL.

Thom~

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Thom A - Wednesday, January 3, 2018 11:47 AM
GilaMonster - Wednesday, January 3, 2018 11:21 AM
sp_statement_completed & sql_statement_completed and look for sp_executesql or EXEC with brackets.
Or the entire batch with sql_batch_completed and RPC completed. Eitherway, a second filter is necessary, and probably better to do it after capturing, especially for the EXEC( one
That would work for SQL generated on the SQL server, but isn't going to help with dynamic SQL generated on an application. From experience, I've found applications are far worse for it, it seems that (some) application/web developers don't even consider; and then as a DBA you see the application code and have a melt down. :'(
Unfortunately, I can't really think of a way you could capture dynamically generated SQL from an application. It is, one of the reasons why I prefer only letting an application use SPs; as then it can't even try to use dynamically generated SQL.

No. That will likely come through from the app as a sql_batch_completed event (parameterised stuff tends to show up as rpc_completed with a call to sp_executesql, at lease ORMs show up that way), so you should pay attention specifically to that event when it's not from SSMS.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 8

GilaMonster - Wednesday, January 3, 2018 11:21 AM
sp_statement_completed & sql_statement_completed and look for sp_executesql or EXEC with brackets.
Or the entire batch with sql_batch_completed and RPC completed. Eitherway, a second filter is necessary, and probably better to do it after capturing, especially for the EXEC( one

Ah... ok. So it uses the same method as doing a code search. Wouldn't it just be easier to review the code so that you don't have to filter through all of the duplication that such a method will generate? Also, that will only capture the code that's currently being used. It may not capture code that's not frequently used.

And, yeah... I agree... no matter what, a secondary filter is absolutely necessary. Hopefully such a filter isn't the same human that wrote the code to begin with.

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

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

All those options are nice, but you still need to check for the front end code. Back when I started coding, we used to concatenate sql strings on the front end and would appear as ad-hoc calls in the database.
I know that there are some tools that help to identify vulnerabilities, but I don't use any of them and can't recommend them.

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: 398694 More actions · Answer 10

Jeff Moden - Wednesday, January 3, 2018 10:49 AM
grant@scarydba.com - Wednesday, January 3, 2018 8:53 AM
juniorDBA13 - Wednesday, January 3, 2018 7:05 AM
I understand all of this.
My questions was how to identify if you are currently vulnerable to attacks and how to find the queries, stored procedures etc that might be causing the problem
You can monitor your server using Extended Events to capture the queries, both rpc_completed and sql_batch_completed. You can pretty quickly begin to assess if the queries coming in are likely to be problematic (and if it's dynamic T-SQL without parameters, it is problematic, period).
How do you distinguish what's dynamic SQL v.s. non-dynamic SQL using Extended Events?

What Gail said. Like I said, assess. ExEvents doesn't do it for you, unfortunately.

"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: 1003870 More actions · Answer 11

GilaMonster - Wednesday, January 3, 2018 12:07 PM
Thom A - Wednesday, January 3, 2018 11:47 AM
GilaMonster - Wednesday, January 3, 2018 11:21 AM
sp_statement_completed & sql_statement_completed and look for sp_executesql or EXEC with brackets.
Or the entire batch with sql_batch_completed and RPC completed. Eitherway, a second filter is necessary, and probably better to do it after capturing, especially for the EXEC( one
That would work for SQL generated on the SQL server, but isn't going to help with dynamic SQL generated on an application. From experience, I've found applications are far worse for it, it seems that (some) application/web developers don't even consider; and then as a DBA you see the application code and have a melt down. :'(
Unfortunately, I can't really think of a way you could capture dynamically generated SQL from an application. It is, one of the reasons why I prefer only letting an application use SPs; as then it can't even try to use dynamically generated SQL.
No. That will likely come through from the app as a sql_batch_completed event (parameterised stuff tends to show up as rpc_completed with a call to sp_executesql, at lease ORMs show up that way), so you should pay attention specifically to that event when it's not from SSMS.

Ah... now I'm baggin' what you're rakin'. It's a way to check the front end code from SQL Server.

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