May 9, 2012 at 12:17 pm
Hello. I have a question about a third-party vendor's SQL Server coding, which is worrying me quite a bit. Here's a sample stored procedure that's used in their program:
SET NOCOUNT ON;
DECLARE @Counter2 int
DECLARE @ItemCode2 nvarchar(30)
SET @Counter2 = @Counter
SET @ItemCode2 = @ItemCode
SELECT *FROM
InventoryItem WHERE (ItemCode = @ItemCode2 OR @ItemCode2 IS NULL) AND (Counter = @Counter2 OR @Counter2 IS NULL)
My question is whether it's possible to execute a SQL injection attack by simply passing in an injection-based string into either @Counter2 or @Itemcode2 that would evaluate the WHERE statment to have a 1=1 selection clause. As the entirety of the procedure is listed above, there does not seem to be any sort of sanitization of parameters, and the two extra variables, @Counter and @Itemcode, seem to just be used to store the user's input before passing it back to be used in the statement (input is passed to those variables at the time of the procedure's call).
Naturally, I would simply test this myself to determine whether the vulnerability is there or not (and to me, it seems like there definitely is one), but... The vendor's program doesn't have a testing environment nor does it have supplied DDL, so we're not allowed to run tests against their coding. This is another red flag, in my opinion, but I'm still rather new to SQL coding, so this might not be all that unusual.
In any case, please advise me on the specifics of this situation, and let me know if I'm being overly paranoid or not. Thank you!
- 😀
May 9, 2012 at 12:26 pm
Really not enough information to give a solid answer. The code itself, it could easily be vulnerable. The real question that we can't answer is if the vendor has code in place in their application to defend against SQL injection.
You would have to setup the vendors application in a sandbox environment and simply test it from the application side to determine if it doing what is necessary.
May 9, 2012 at 12:27 pm
If this is a parameterized proc this should be ok. Does the procedure code look like this?
create procedure SomeProc
(
@Counter2 int,
@ItemCode2 nvarchar(30)
) as begin
SELECT * FROM
InventoryItem WHERE (ItemCode = @ItemCode2 OR @ItemCode2 IS NULL) AND (Counter = @Counter2 OR @Counter2 IS NULL)
end
The above would be an example of a proc that is safe from sql injection. It is vulnerable when you execute code directly either by building a string in the application of executing dynamic sql in a proc.
Does that help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2012 at 12:39 pm
No, that code as you posted can't have any SQL injected. That said, it's very inefficient.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
To have a SQL Injection vulnerability requires that some user input be concatenated into an build up string which is then executed.
http://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/
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
May 9, 2012 at 12:41 pm
Thank you for the replies.
Lynn, the software vendor does not give us the option to set up the application in our own environment. It's a cloud-hosted SQL server with a front-end that basically calls the stored procedures in the applicaton's data dictionary. We're given free reign to look around the data dictionary, but we can't directly call any of the procedures; they can only be called from various menu buttons and so forth. However, some procedures do take user input, which is where my concerns about the vulnerability originated from. Furthermore, I believe (though I'm not fully certain) that we're hosted within the same SQL Server instance as a few other users, so the potential of calling SELECT * FROM sys.databases could exist within the manipulation of some of the procedures.
Sean, I'm not sure if it helps or not. The code above is exactly how the procedure is stored and called from within the application, if what I've gotten out of their customer support staff is accurate. All of the procedures are passed around by scripts, so what's within the data dictionary should be how the procedures are called verbatim.
EDIT: Thank you as well, Gail. I see; my understanding of SQL injection was a bit off, I believe. I was also alarmed by the fact that the company had been the subject of some SQL injection attacks in the past, and as such their stance on preventing future attacks was not to correct their coding, but rather to amputate functionality from the product that lead to the injection vulnerabilities. With that in mind, I was quite concerned that the possibility for further injections existed. That aside, I feel a bit more confident about their capabilities at present, though there's some (non-SQL) further complications I need to resolve with their support as well. Thank you very much, everyone.
- 😀
May 9, 2012 at 12:43 pm
hisakimatama (5/9/2012)
Thank you for the replies.Sean, I'm not sure if it helps or not. The code above is exactly how the procedure is stored and called from within the application, if what I've gotten out of their customer support staff is accurate. All of the procedures are passed around by
scripts, so what's within the data dictionary should be how the procedures are called verbatim.
Then as both myself and Gail suggested the code should be safe from injection.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2012 at 12:43 pm
Sql injection is not just about the code, permissions play a part too. Obviously, ensure the database connection has only the privileges it requires.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 9, 2012 at 1:32 pm
Hrm. Upon looking further into their coding, I've stumbled upon this:
SET NOCOUNT ON;
DECLARE @whereClause2 nvarchar(1000)
SET @whereClause2 = @whereClause
set @whereClause2 = ISNULL(@whereClause2,'')
declare @sql nvarchar(1000)
set @sql = '
SELECT CategoryCode, ItemCode, IsPrimary, CategoryDescription
FROM InventoryItemCategoryReportView ' + @whereClause2
print @sql
exec (@sql)
That use of @whereClause2, which takes user input (again from an input box) and sticks it to the end of a pre-generated SQL statement... If my refreshed understanding of SQL Injection is correct, that would be an injection attack point, wouldn't it?
Sorry if I'm being overly paranoid here, but my paranoia meter is running at red alert considering the bits and pieces of information I've picked up about this company.
- 😀
May 9, 2012 at 1:36 pm
Yup, that code is vulnerable to SQL Injection. If it's taking the value straight from user input with no sanitisation, no validation, that's one mighty big hole right 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
May 9, 2012 at 1:44 pm
GilaMonster (5/9/2012)
No, that code as you posted can't have any SQL injected.....
To have a SQL Injection vulnerability requires that some user input be concatenated into an build up string which is then executed.
http://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/
Wouldn't it be possible that the code that calls that to be vulnerable to SQL injection though? If anything is building a string with a SQL command and executing that string that's a point at which SQL injection can happen even if that concatenation isn't happening in SQL. The vulnerability just isn't in the SQL code, it's in what's calling it.
May 9, 2012 at 1:50 pm
cfradenburg (5/9/2012)
Wouldn't it be possible that the code that calls that to be vulnerable to SQL injection though?
Yes, but it's also possible that it isn't. Can't draw any conclusions at all about code that we can't see.
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
May 9, 2012 at 2:57 pm
Thanks, Gail, both for correcting my misunderstanding on spotting SQL injections and for verifying my concerns about the new problems I found. Our business will probably veer away from this company, both because of this issue (which I was able to exploit with a simple query of sys.databases), and other unrelated problems. Glad to see my persistent paranoia isn't always unfounded, at least 😛
- 😀
May 9, 2012 at 3:01 pm
hisakimatama (5/9/2012)
Hrm. Upon looking further into their coding, I've stumbled upon this:
SET NOCOUNT ON;DECLARE @whereClause2 nvarchar(1000)
SET @whereClause2 = @whereClause
set @whereClause2 = ISNULL(@whereClause2,'')
declare @sql nvarchar(1000)
set @sql = '
SELECT CategoryCode, ItemCode, IsPrimary, CategoryDescription
FROM InventoryItemCategoryReportView ' + @whereClause2
print @sql
exec (@sql)
That use of @whereClause2, which takes user input (again from an input box) and sticks it to the end of a pre-generated SQL statement... If my refreshed understanding of SQL Injection is correct, that would be an injection attack point, wouldn't it?
Sorry if I'm being overly paranoid here, but my paranoia meter is running at red alert considering the bits and pieces of information I've picked up about this company.
Is the data comng from a windows form or a web form? If there validations in place at the data entry point then sure it could be safe, however still not ideal.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 9, 2012 at 7:57 pm
I'm not sure what the distinction is there, Perry; I'm assuming Windows form would mean something coded in a language such as Java, C#, C++, etc.? Either way, I believe it's coded in a non-web-based language, though I can't be fully certain, as we're not allowed to look into the details of the code. In any case, I tried tossing a simple ";SELECT * FROM sys.databases" into the input box (minus the double quotes), and certainly enough, I got a list of all the databases on our platform. That's a glaring vulnerability point, and I'd much rather not deal with it myself.
We're not allowed to code over the vulnerability, and anyone else on the same cloud server as us could do the same. I would try to go further and see if I could view the information in the other companies' databases just to prove a point when I write a new response to the company, but I'm quite certain that breaches various ethical and legal boundaries, so I'll refrain from that and simply express my distaste with the situation in our cancellation letter.
- 😀
May 10, 2012 at 12:27 am
hisakimatama (5/9/2012)
I'm not sure what the distinction is there, Perry; I'm assuming Windows form would mean something coded in a language such as Java, C#, C++, etc.? Either way, I believe it's coded in a non-web-based language
What do you mean web based language? Asp.net web pages still use either vb.net or c# in their code behind pages.
A web form is a website and a windows form is a windows dialog based application.
The point I'm making is that data may be validated at the point where it's entered. For instance, an input text box may have validation rules coded in the application or may even be using .net validations to ensure that what is passed to the SP is valid. There may be a validation check for certain characters such as -- and " and ; etc.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply