SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Thom A
Thom A
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87149 Visits: 22285
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
GilaMonster
GilaMonster
SSC Guru
SSC Guru (920K reputation)SSC Guru (920K reputation)SSC Guru (920K reputation)SSC Guru (920K reputation)SSC Guru (920K reputation)SSC Guru (920K reputation)SSC Guru (920K reputation)SSC Guru (920K reputation)

Group: General Forum Members
Points: 920072 Visits: 48862
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
Jeff Moden
SSC Guru
SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)

Group: General Forum Members
Points: 897096 Visits: 48302
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166096 Visits: 22834
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
Grant Fritchey
SSC Guru
SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)SSC Guru (362K reputation)

Group: General Forum Members
Points: 362240 Visits: 34467
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

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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)SSC Guru (897K reputation)

Group: General Forum Members
Points: 897096 Visits: 48302
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search