Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Hex on Your Database


A Hex on Your Database

Author
Message
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
Thanks for this clarification, Tao!

Best Regards,

Chris Büttner
Jamie-2229
Jamie-2229
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 831
"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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
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.
Raghuram (AJ)
Raghuram (AJ)
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 299
Awesome question! Quite scary too... Whistling

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
SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1479 Visits: 711
Yah me to. Can't see it on the screen... gonna try the sleect trick.
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
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.
Bradley Deem
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1248
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. Sad 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!
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