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 ««12345»»»

A Hex on Your Database Expand / Collapse
Author
Message
Posted Friday, June 6, 2008 5:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 2,840, Visits: 3,872
Thanks for this clarification, Tao!

Best Regards,
Chris Büttner
Post #512806
Posted Friday, June 6, 2008 5:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
"THIS"


There is an adjustable bar on my page - can anyone tell me how that was coded. I'm assuming when the question refers to "... this" that the meaning of it is that there is an adjustable bar on the page. Since I have one on every page. I'm changing my mind - I don't think "this" can actually cause me to have an error. I really like the way "this" shows up on my question. Please tell me how you coded "this"! I am very curious.


Jamie
Post #512808
Posted Friday, June 6, 2008 7:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Just for some history on this, a major worm recently went around the net using exactly this kind of exploit. Instead of a simple select command, it installed a Java applet that would send data to some server in China.

Here are some of the stories:

http://isc.sans.org/diary.html?storyid=4519
http://www.sqlservercentral.com/Forums/Topic495160-359-1.aspx
http://blog.washingtonpost.com/securityfix/2008/04/hundreds_of_thousands_of_micro_1.html
http://isc.sans.org/diary.html?storyid=4393
http://www.shadowserver.org/wiki/pmwiki.php?n=Calendar.20080507

The wording of the question was awkward, no doubt about it. Sorry about that. I tried to think of a way to put this question together (someone else suggested I submit it), but couldn't come up with anything I was happy with. So I sent in the best I could think of. (The easy part was the "clever" title for the question.)


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #512893
Posted Friday, June 6, 2008 7:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Hugo Kornelis (6/6/2008)

Mike,

My assumption on reading the questing was that "account=" is generated by the page, and the rest comes from an input box. The user is supposed to just enter a number, and SQL Server appends that to some statement (so that "account=1" forms the last part of an unfinished SQL stattement).

In this case, a hacker tries his luck by entering "1;declare @a varchar(1000);set @a=cast(blahblah as varchar(1000));exec(@a)". Though the question could have been worded clearer, this is a great QotD in that it shows that even after doubling quotes and checking for banned keywords, dynamic SQL can still be abused to gain access to the server.


The part demonstrating the hex encoding was a nice example, but the account=1 part could have used some clarification. This example definitely demonstrates that a developer who allows code like this should be immediately banished to the unemployment line, since the UI is expecting a number ("1") and the user is allowed to enter over 100 characters, many of them non-numeric.
Post #512897
Posted Friday, June 6, 2008 7:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Tao Klerks (6/6/2008)
Hmm, I guess I knew where the author was going and got the answer right, but I disagree with the premise of the question.

The asssumption that you can prevent SQL injection by combining "quote doubling" with "keyword detection" is simply silly. Quote doubling is only ever of any use in striong values (values that you encapsulated in quotes in your SQL string)

There are, however, very simple ways that you CAN safely prevent injection with a very similar method:

1) Always know the "type" of the data that the user is submitting in a given variable/field.
2) Whenever inserting a user-provided character string (text/nvarchar/etc), replace (double up) single quotes
3) Whenever providing any other sort of value, validate it as really being of that type BEFORE trying to use it in any dynamic SQL. If it is supposed to be a number, check it is numeric; if it is supposed to be a date, check it really is. And of course, if it is not of the type it is supposed to be, never ever allow it to be used in dynamic SQL.

When these simple, easy-to-implement rules are consistently followed there is no way to perform an injection attack (as far as I know).

PLEASE NOTE: The problem with this method is that there is no reliable way to establish, over any significant amount of code, that the above rules really were followed consistently - so it is much safer to just stick to ADO Parameters and pre-defined SQL statements, or Stored Procedures with Parameters. (and the following does not count: "EXEC MyProc @SomeVar = " & SomeUserProvidedVariable & "; " - this is exactly the same as regular dynamic SQL)

Does anyone know of any way that a SQL injection could actually be performed, if the 3 rules above were consistently followed?


The problem I was bringing up is that, according the news articles in May this year, thousands of servers did NOT have adequate SQL injection prevention measures, and according to at least two threads on SSC's forums, there are DBAs/Devs who think you can prevent SQL injection in exactly the way I described.

The whole point of the question is that preventing SQL keywords in the variables part of a URL doesn't work. Judging by the news on Internet Storm Center, there are still problems with this.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #512900
Posted Friday, June 6, 2008 7:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Tao Klerks (6/6/2008)
Hi, the sample actually assumes old-style ASP (usually coded in VBScript), but the point is not the extraction of the list of databases.

Getting the list of databases using this technique would be quite hard, because the first/intended statement, ending in the user-supplied "1", will have completed successfully and be returned to the calling code - the list of databases would be a second recordset, most likely ignored by the code (in ASP there is no automatic handling at all, and in ASP.Net I do not know of any controls that auto-render multiple recordsets).

The more scary consideration (the point of the question, I believe) is that any vandalism would at that point be possible, depending on the rights of the SQL user the code is running under, and possibly even doing things to gain "full" access to the database or server by other means (resetting passwords, running commands on the command-line, etc).


You can parameterize SQL statements using classic ADO/ASP as well. It's just a lot easier to use string = string + value than to do it right, so a lot of people carried their bad VB6 coding habits over to .NET.
Post #512906
Posted Friday, June 6, 2008 7:55 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:53 PM
Points: 494, Visits: 299
Awesome question! Quite scary too...

The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
Post #512910
Posted Friday, June 6, 2008 7:56 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Yah me to. Can't see it on the screen... gonna try the sleect trick.
Post #512912
Posted Friday, June 6, 2008 8:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
GSquared (6/6/2008)
[quote]
The problem I was bringing up is that, according the news articles in May this year, thousands of servers did NOT have adequate SQL injection prevention measures, and according to at least two threads on SSC's forums, there are DBAs/Devs who think you can prevent SQL injection in exactly the way I described.

The whole point of the question is that preventing SQL keywords in the variables part of a URL doesn't work. Judging by the news on Internet Storm Center, there are still problems with this.


I agree that these methods arent' foolproof, but assuming you're really "preventing SQL keywords in the variables part of the URL" the string passed in would be rejected based on the keywords below (I bolded and underlined them - they're all SQL Server reserved keywords):

declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)

You're right though, scanning for keywords is not the best method for dealing with these types of issues especially when you're dealing with values that can be parameterized like predicate constants and values.
Post #512919
Posted Friday, June 6, 2008 8:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:17 PM
Points: 554, Visits: 1,193
Good question, but the answer leaves me a bitter. I assumed Stored Procedures were in use with proper parameter types. (which is what I use). So SQL injection would not have occured. Then I get to the answer and find out that I'm wrong "IF"... arg. Perhaps clarify the question?

This is a currently common SQL injection attack. If the web page does not use stored procedures, but instead uses dynamic SQL, this is a valid SQL 2005 command (there are versions for SQL 2000), and might execute.


*EDIT*
My apologizes bitter was a harsh word, maybe its cause I was doing so well with the QOTD. Either way this caused some brain action and for that thanks!
Post #512923
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse