Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Vulnerable to SQL Injection third-party API Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
Not sure where this post should go. . .

I have a third party API that I pass data from my database to. The UI front end of my database allows the user to input string data, such as: -

+5';DROP/**/TABLE[t1];COMMIT;--

This is properly parametrised on my end so is inserted into my database without executing the "DROP TABLE" command. However, the third party API takes the data I pass to it and builds a SQL statement which means that it ends up executing the "DROP TABLE".

There is nothing I can do about the code in the third party API and they have insisted that this is a bug in my software and not theirs. I disagree, but arguing the case any further is futile. I was thinking about passing the string through to them as something like: -

stringVariable.Replace("'", "''")

I know that parametrising the query would be better, but can anyone see an obvious way to circumvent this "protection" ?



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1535468
Posted Tuesday, January 28, 2014 12:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Cadavre (1/28/2014)
Not sure where this post should go. . .

I have a third party API that I pass data from my database to. The UI front end of my database allows the user to input string data, such as: -

+5';DROP/**/TABLE[t1];COMMIT;--

This is properly parametrised on my end so is inserted into my database without executing the "DROP TABLE" command. However, the third party API takes the data I pass to it and builds a SQL statement which means that it ends up executing the "DROP TABLE".

There is nothing I can do about the code in the third party API and they have insisted that this is a bug in my software and not theirs. I disagree, but arguing the case any further is futile. I was thinking about passing the string through to them as something like: -

stringVariable.Replace("'", "''")

I know that parametrising the query would be better, but can anyone see an obvious way to circumvent this "protection" ?


This is why high velocity pork chops were created. If you're absolutely 100% psitive that it's there code and that you could make a trial jury understand it, then exercise your rights as a consumer. Tell them that if they don't fix it, then you're going to publish your findings on the internet to protect other people from their crap code and that you're going to drop them like a hot potato in the meantime. If your company doesn't want to get involved, make sure that anything that you publish has the disclaimer that you're publishing the fault as an independent agent not associated with any company.

If that's too high profile for you (and I wouldn't blame you if you felt that way), then tell them that you're just going to recommend to all your friends and contacts that they never have anything to do with them.

These 3rd party companies need to start taking responsibility for their crap code.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1535592
Posted Wednesday, January 29, 2014 5:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
Thanks for taking the time to reply Jeff.

Unfortunately, none of the options you've laid out are viable for me. If I did any of them, the only loser would be me.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1535851
Posted Wednesday, January 29, 2014 7:30 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:08 AM
Points: 80, Visits: 260
Along the lines of what Jeff suggested, maybe you can implement your fix and publish it as a "fix" to a security hole in their api? At the very least, you should communicate your findings to your business and get their input on the matter. I would imagine if you show them tables can be dropped, they will dedicate resources to fix the problem.

Please note, I am not a lawyer so please investigate possible ramifications before publishing security holes. You could start at: https://www.eff.org/issues/coders/vulnerability-reporting-faq


Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1535911
Posted Wednesday, January 29, 2014 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Cadavre (1/29/2014)
Thanks for taking the time to reply Jeff.

Unfortunately, none of the options you've laid out are viable for me. If I did any of them, the only loser would be me.


Understood. That's the bummer about these things. No responsibility on the part of our own companies nor the 3rd parties and we get left holding the bag because we have mouths to feed. If our system gets hacked because of the 3rd party fault, it somehow becomes our fault.

At this point, my suggestion would be to make sure you keep all emails and other correspondence on a thumb drive that you take home or just forward the emails to your private email so that if someone tries to throw you under the bus for the hack, you have proof that you warned everyone and took the necessary steps to try to overcome the shortfall that no one but you paid attention to.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1535943
Posted Wednesday, January 29, 2014 9:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
Jeff Moden (1/29/2014)
Understood. That's the bummer about these things. No responsibility on the part of our own companies nor the 3rd parties and we get left holding the bag because we have mouths to feed. If our system gets hacked because of the 3rd party fault, it somehow becomes our fault.

At this point, my suggestion would be to make sure you keep all emails and other correspondence on a thumb drive that you take home or just forward the emails to your private email so that if someone tries to throw you under the bus for the hack, you have proof that you warned everyone and took the necessary steps to try to overcome the shortfall that no one but you paid attention to.


That's essentially what I've done so far. The bug has been there since day one and hasn't been spotted until now, but I really don't see why that is relevant.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1536002
Posted Wednesday, January 29, 2014 3:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Ya just gotta love the people that say things like "Well, it's always been that way" after you point out a major problem to them.
{EDIT} I just realized that could be taken two ways. This was directed at the 3rd part vendor.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536122
Posted Thursday, January 30, 2014 12:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:45 AM
Points: 893, Visits: 2,477
Classic second order SQL injection.

Anonymous post on security.stackexchange.com?

May I recommend a CHECK constraint on the column(s) that prevent any data except letters and maybe spaces, perhaps numbers if you have to? If you have to accept any other characters, you're going down a very dark road indeed. Whitelisting is far better than trying to blacklist, but still not perfect.


As far as evading quote checking, here's a starter example showing a single-quote without having a single-quote in it:
DECLARE @test CHAR(6)
SET @test = 0x277465737427
PRINT @test

As an exercise to the reader, follow this up with sp_executesql, exec(), and so on, and play with CHAR(27).

There's a Unicode variant at http://security.stackexchange.com/questions/3611/sql-injection-but-why-isnt-escape-quotes-safe-anymore

The other common way is string truncation - a ' as the very last position still ends up as a ' in the database.

For SQL Server in particular, play with:
set quoted_identifier off
print "test"

The same question on another site:
http://stackoverflow.com/questions/15537368/how-can-sanitation-that-escapes-single-quotes-be-defeated-by-sql-injection-in-sq

Another version of the question:
http://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use
Post #1536508
Posted Thursday, January 30, 2014 12:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:39 AM
Points: 886, Visits: 616
As an alternative, could the application connect to SQL via an account that doesn't have rights to execute DDL statements, or wold this break your software?

John
Post #1536514
Posted Thursday, January 30, 2014 1:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
Nadrek (1/30/2014)
May I recommend a CHECK constraint on the column(s) that prevent any data except letters and maybe spaces, perhaps numbers if you have to? If you have to accept any other characters, you're going down a very dark road indeed. Whitelisting is far better than trying to blacklist, but still not perfect.


The data requires a lot more "allowed" characters than that. The database and the software that I have any control over is safe, it's a third-party API and their database that is vulnerable.

Thanks for the links.

john barnett (1/30/2014)
As an alternative, could the application connect to SQL via an account that doesn't have rights to execute DDL statements, or wold this break your software?

John


Not my software or my database at fault. I connect via their API to their database, I can't mess with anything on their systems (technically I could in-house, but it isn't a viable option unfortunately).

To be honest here guys, I don't think there's a whole lot more I can do.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1536528
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse