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
juniorDBA13
juniorDBA13
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 299
Is there a way to identify if there is any dynamic sql in use in a database that may be vulnerable to sql injection?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555055 Visits: 47752
Anything that concatenates user input into a string and executes the resulting string is vulnerable. That includes user input that got stored in the database and then used to build a string.

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


juniorDBA13
juniorDBA13
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 299
Yes but we support a number of databases and dont have time to check every query so would like someone way to check the databases for vulnerabilities
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555055 Visits: 47752
The easiest (and safest) is to assume that all dynamic SQL is vulnerable, unless proven otherwise.

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


HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6932 Visits: 4513
A starting point may be to query stored procedures for the existence of sp_executeSQL.

...
Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46182 Visits: 15686
HappyGeek - Wednesday, January 3, 2018 6:30 AM
A starting point may be to query stored procedures for the existence of sp_executeSQL.

A lot of people, however, tend to use EXEC(@SQL) which'll be missed.



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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224034 Visits: 33571
Flat out, if you're not parameterizing the queries in some method, through stored procedures, through the code, or through parameters defined in sp_executesql, you are almost absolutely at risk of SQL Injection attacks. Period. Full stop. Doesn't matter if it's one query or one million. You're at risk if you're not using methods that ensure only appropriate data can be sent into queries in your system. The way to ensure that is using parameters. That's it.

Now, none of this says you MUST use stored procedures (I hate that argument). However, just generating dynamic T-SQL, using pretty much any method, can be vulnerable unless that T-SQL is parameterized. You can see in the code examples that I link to, you're not limited to stored procs. However, you have to write the code correctly, or your business is vulnerable.

To quote my kids, it's current year. No one should be writing code any longer that isn't dealing appropriately with this issue.

----------------------------------------------------
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512238 Visits: 44311
juniorDBA13 - Wednesday, January 3, 2018 6:04 AM
Yes but we support a number of databases and dont have time to check every query so would like someone way to check the databases for vulnerabilities


A little tough love coming up here... If you don't take the time to check every instance of dynamic SQL for inject-ability, then plan on spending some time explaining how your company suffered a successful attack. You CAN'T afford to not take the time. Tell the company to stop making excuses and do it NOW!

Then implement a rigorous process that prevents unreviewed code from being deployed even to your staging environments. Again, no excuses... just do it!

I'll also tell you that you need to do the same thing for your front end code. At the very least, hire a 3rd party to do penetration testing of your public facing applications and by "public facing", I mean any app outside of IT (we even test the non-public facing stuff).

This is something you don't want to screw with or let get balled up in stupid politics by managers that don't know any better. If they think it's expensive to do all of this, wait until they find out the true cost of a successful attack on your systems.

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555055 Visits: 47752
juniorDBA13 - Wednesday, January 3, 2018 6:04 AM
Yes but we support a number of databases and dont have time to check every query so would like someone way to check the databases for vulnerabilities


I should point out that the hacking attack that took Sony down a few years ago started with SQL injection and ended with a complete compromise of their entire network.
The Equifax data breach - SQL injection
etc, etc, etc, http://codecurmudgeon.com/wp/sql-injection-hall-of-shame/

Maybe ask the company's risk officers whether it's worth the time not to join that list.

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


HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6932 Visits: 4513
Thom A - Wednesday, January 3, 2018 6:33 AM
HappyGeek - Wednesday, January 3, 2018 6:30 AM
A starting point may be to query stored procedures for the existence of sp_executeSQL.

A lot of people, however, tend to use EXEC(@SQL) which'll be missed.

Tom you are of course correct, it did occur to me, I hoped the OP would have picked up on that too, it was offered purely as a starting point.


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