January 3, 2018 at 5:34 am
Is there a way to identify if there is any dynamic sql in use in a database that may be vulnerable to sql injection?
January 3, 2018 at 6:00 am
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
January 3, 2018 at 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
January 3, 2018 at 6:11 am
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
January 3, 2018 at 6:30 am
A starting point may be to query stored procedures for the existence of sp_executeSQL.
...
January 3, 2018 at 6:33 am
HappyGeek - Wednesday, January 3, 2018 6:30 AMA 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.
Larnu.uk
January 3, 2018 at 6:48 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2018 at 6:53 am
juniorDBA13 - Wednesday, January 3, 2018 6:04 AMYes 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
Change is inevitable... Change for the better is not.
January 3, 2018 at 6:58 am
juniorDBA13 - Wednesday, January 3, 2018 6:04 AMYes 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
January 3, 2018 at 7:02 am
Thom A - Wednesday, January 3, 2018 6:33 AMHappyGeek - Wednesday, January 3, 2018 6:30 AMA 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.
...
January 3, 2018 at 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
January 3, 2018 at 7:21 am
juniorDBA13 - Wednesday, January 3, 2018 7:05 AMI 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
Are those only select queries or are there also Insert/Update/Delete queries? The reason for asking is that you can use the sys.dm_exec_describe_first_result_set function to check if there is a result set if only select queries are allowed. I did post this on one of the forums a while back.
😎
January 3, 2018 at 7:30 am
juniorDBA13 - Wednesday, January 3, 2018 7:05 AMI 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'll need to check any code that uses dynamic SQL, like the others said; that could be an SP on your SQL server, a trigger, etc, and even your application code.
I suppose a better question, from us might be, do you know how to determine that a piece of SQL could suffer injection? For example, consider the following 2 (fake) SPs that are intended to do the same thing; do you understand why one would suffer from injection and the other would not?
CREATE PROC AllTableData1 @TableName varchar(8000), @CustomerName varchar(100) = NULL AS
DECLARE @sql nvarchar(MAX) = 'SELECT * FROM ' + @TableName +
CASE WHEN @CustomerName IS NOT NULL THEN ' WHERE CustomerName = ''' + @CustomerName +'''' ELSE '' END + ';';
EXEC (@SQL);
GO
CREATE PROC AllTableData2 @TableName varchar(8000), @CustomerName varchar(100) = NULL AS
DECLARE @sql nvarchar(MAX)
SELECT @sql = N'
SELECT *
FROM ' + QUOTENAME([name])
FROM sys.Tables
WHERE [name] = @TableName;
IF @CustomerName IS NULL BEGIN
SET @sql = @sql + N';';
EXEC sp_executesql @sql;
END ELSE BEGIN
SET @sql = @sql + N'
WHERE CustomerName = @dCustomerName;';
DECLARE @Param nvarchar(MAX) = N'@dCustomerName varchar(100)';
EXEC sp_executesql @sql, @param, @dCustomerName = @CustomerName;
END
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 3, 2018 at 7:34 am
juniorDBA13 - Wednesday, January 3, 2018 7:05 AMI 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.
...
January 3, 2018 at 7:52 am
juniorDBA13 - Wednesday, January 3, 2018 7:05 AMMy 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
Check any dynamic SQL, in the DBs or in the application. You have to start with the assumption that any dynamic SQL is vulnerable and work from there.
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
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply