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


Vulnerable to SQL Injection third-party API


Vulnerable to SQL Injection third-party API

Author
Message
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 8472
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" ?


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85658 Visits: 41084
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.
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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 8472
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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
dbassassin
dbassassin
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 290
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85658 Visits: 41084
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.
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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 8472
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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85658 Visits: 41084
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.
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
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1876 Visits: 2726
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
john barnett
john barnett
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1005 Visits: 750
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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 8472
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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
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