June 6, 2008 at 9:35 am
The formatting problem in the web page (the "THIS" thing you're talking about) is a problem with the forum software, if I remember what Steve said about it. It's not something that the person writing the question has control over, so far as I know.
- 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
June 6, 2008 at 9:37 am
Mike C (6/5/2008)
Hmmmm. This is what I get in SSMS:"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='."
Did you only us this portion:
declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)
If you only use that portion and the server you are connected to is 2005, it brings up a list of all databases. The hex works out as:
select name from sys.databases;
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 6, 2008 at 9:37 am
As far as the wording of the question goes, some people ended up misreading the intent of the question, one person even ended up bitter about it. I understand that, and that's what I'm appologizing about. Not any one particular beef with it, but the general beef that the question could/should be more clear. I just couldn't come up with something I really liked regarding the wording, that fit in the format for a QotD. I decided to go ahead with it anyway, because it seems like something that could spark a good discussion.
- 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
June 6, 2008 at 9:46 am
The idea is nice and it runs in SQL Server nice. But the web developers code their pages differently and appending this to different asp and aspx pages going towards SQL Server, I got a mix of nothing - the page just runs well (This is our favorite: "On Error Resume Next")
or data type or object errors. I never got a list of databases.
Regards,Yelena Varsha
June 6, 2008 at 10:25 am
Couldnt read the question. Mine looked just like the screen shot earlier in the thread. Boo. 🙁
June 6, 2008 at 10:36 am
timothyawiseman (6/6/2008)
Mike C (6/5/2008)
Hmmmm. This is what I get in SSMS:"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='."
Did you only us this portion:
declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)
If you only use that portion and the server you are connected to is 2005, it brings up a list of all databases. The hex works out as:
select name from sys.databases;
I used everything that was provided. The question seemed a little unclear to me, and I ended up with two possible choices: (a) don't take the question at face value and implicitly ignore the account=1; part, or (b) take the question at face value (e.g., a trick question, which does happen occasionally). I tend to take questions at face value, because when I don't they end up being trick questions that you weren't supposed to take at face value 🙂
June 6, 2008 at 1:56 pm
It is important to point out, since I saw a reference to web applications, that SQL injection is not just limited to web applications. Any time you take unsanitized input and use it in this manner, you have the potential for a problem. This includes fat clients and it includes stored procedures, etc., called by other stored procedures.
The bottom line is that if you're taking input, you need to validate it. Parameterization doesn't completely eliminate the vulnerability, as with the example Hugo gave. BTW, I have seen that in code, as many others have. The reason we see it that way is because there is some requirement that stored procedures must exclusively be used. So the coder short circuits the intent by complying with the exact wording of the standard.
K. Brian Kelley
@kbriankelley
June 6, 2008 at 3:27 pm
Hey GSquared - thanks for having such a good attitude about the comments on your QOTD. I get burned by misreading a QOTD every once in awhile -the one that really hurt was a 47 pointer. 😛 I don't think I'll ever get my percentage back up.
I tried my hand at writing a QOTD once though and they are a beast to write - trying to work through the wording, the syntax, hoping it hasn't been asked before, making it not too hard, but not simplistic either... I decided after that I was just happy to give it a good shot every day. Thanks for putting one in for us to work on. Keep it up.
Chad
June 9, 2008 at 12:29 am
Nice one... but it should open lot of eyes.......:)
June 9, 2008 at 1:21 am
Hmmm, interesting topic. Definitely something to be aware of. I see alot of "Dynamic SQL is evil" posts but one thing sticks out at me that is an old C/C++ trick I use (It's a good way to ensure that you don't mix up your "==" and "=").
If, for example, the "account=1" is used as...
"SELECT * FROM Accounts WHERE AccountNumber=" & Request.QueryString("account")
Then changing this query to...
"SELECT * FROM Accounts WHERE " & Request.QueryString("account") & "=AccountNumber"
Returns an error.
Just my 2c...
June 9, 2008 at 3:07 am
Switching the order is no guarantee the hacker could modify the string to:
account=1=1;declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a);--
The additional "=1" now returns all records from your planned table and the "--" comments out the rest of SQL.
It seems we must still check the value is what we expect 🙁
Another great question getting the grey matter going.
June 9, 2008 at 8:30 am
We recently dealt with this exploit.
One very useful tip that I did not find anywhere else: Revoke access to the meta-data tables this attack uses to find every character field in every table. If not for this obvious security risk, the attacker would need to _know_ the name of each field. As it is, this drive-by vandalism is pretty easy to do.
I wrote about this in response to an auditing post: http://www.sqlservercentral.com/Forums/FindPost513739.aspx
part of the heuristics we examine before the SQL Command Text is sent to the server is to look for the "varbinary" keyword as well as the "cast(0x" because our normal webserver-generated transactions are never going to legitimately use those commands. If this exploit vector uses tokens that are never otherwise used in real transactions, it's easy to identify the entire family of attack by this signature (and prevent even passing the command to the database).
June 9, 2008 at 8:54 am
First off, I thought the QotD was great. I've read about this attack, but hadn't yet seen an example of how it was executed.
scott (6/9/2008)
Hmmm, interesting topic. Definitely something to be aware of. I see alot of "Dynamic SQL is evil" posts but one thing sticks out at me that is an old C/C++ trick I use (It's a good way to ensure that you don't mix up your "==" and "=").If, for example, the "account=1" is used as...
"SELECT * FROM Accounts WHERE AccountNumber=" & Request.QueryString("account")Then changing this query to...
"SELECT * FROM Accounts WHERE " & Request.QueryString("account") & "=AccountNumber"Returns an error.
Just my 2c...
This is just security through obscurity. There are several problems with this approach. First, many developers who are too lazy to properly parameterize the statement (which would be the correct way to fix this), are also likely to display the error message returned from SQL server somewhere on the page. For example, the error message I get from your solution is this: "An expression of non-boolean type specified in a context where a condition is expected, near ';'". So I can simply change my attack to this:
1=1;{add my hex attack here};--
The 1=1 will prevent the error, and then by ending the attack with the line comment I can remove the trailing command from the query definition.
The way to properly prevent ANY and ALL injection attacks has already been stated:
1) use Parameter objects along with a Command object
2) If you MUST dynamically add other clauses to your statement, NEVER, NEVER, NEVER use the user's direct input as part of your concatenation.
examples of #2:
dynamic ORDER BY
string cmd = "SELECT col1, col2 FROM myTable ORDER BY " + Request.QueryString["input"]
dynamic procedure name
string cmd = "myStoredProc" + Request.QueryString["input"]
DbCommand query = new DbCommand(cmd);
query.CommandType = CommandType.StoredProcedure
.....
In the case of #2 use an enumeration or a whitelist of some kind if you must write statements like those above.
The sad thing here is that the answer to this for many will simply be to update their blacklists or some other hack instead of properly fixing the issue which has been so well documented. IMHO, I blame poorly written documentation, poor articles posted on the web and lazy (read: poorly written) forum responses which suggest (implicitly) to the reader that it is ok to write SQL which is wide open to these attacks. When writing any of the above the other should take the responsibility to write examples properly and assume that for every 1 person who knows to translate the example to a proper statement there will be 100 noobs who will copy and paste his example into their code and never give it a second thought.
The repercussions of not properly writing ad hoc queries is serious. Getting the list of databases is tame by any measure. It would be so easy for the attacker to upload an XSS attack to a forum and place malware on all your visitor's machines. This should be such an easy thing to fix, but too many people think, "It won't happen to me".
June 9, 2008 at 5:22 pm
AGAIN. The question makers should take some English language classes.
What sort of question is this:
Q: What happens?
A: The list of databases.
How can a "list of databases" happen?
The correct question should be something like ".... The Request string gets executed... What will be returned as the result of the execution?"
The correct answer should be something like ".... the list of databases will be returned"...
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
June 10, 2008 at 11:20 am
Yes, a list of databases happens.
As far as English classes, I'd gladly take one, but I actually find my state of complete illiteracy provides me with more opportunities to actualize my conceptual mazeway in manners that degrade misinterpretation. Just to abjure obfuscation, I'm nearly certain to employ a titanically substandard technique and mastery of both vocabulary and structure.
(As already mentioned, I wasn't actually happy with the wording of the question, and clarification and expansion of the answers would have been good, but I couldn't work out how to ask it perfectly within the constraints of the format. I do, however, ask that you avoid equating a possible awkwardness of a single part of the concept, with a lack of skill in English.)
- 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
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply