A Hex on Your Database
-
Comments posted to this topic are about the item A Hex on Your Database
- 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
-
Maybe I'm missing something here. This statement, executed as dynamic SQL in SQL Server:
account=1;declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)Is supposed to generate something other than an error along the lines of "Incorrect syntax ..."?
I'm going to paste this into SSMS and see what it says, but I suspect it's going to error since "account=1;" is not valid SQL as far as I can tell.
-
Hmmmm. This is what I get in SSMS:
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='."
-
My years of reading hex as an assembler/C programmer has finally paid off in SQL!
-
Steve (hope you're reading),
The code snippets are completely unreadable on my computer (Windows XP, running Internet Explorer 7. Scrrenshot is below:
(Since I found no way to attach an image, I had to upload it to my personal webspace. I will someday in the future remove it again since I have limited space available).
I was able to get to the text by selecting text starting before and ending after the code and than copying and pasting into a text document, but I don't think it's supposed to be that way 😀
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Mike C (6/5/2008)
Maybe I'm missing something here. This statement, executed as dynamic SQL in SQL Server:account=1;declare @a varchar(1000);set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B as varchar(1000));exec(@a)Is supposed to generate something other than an error along the lines of "Incorrect syntax ..."?
I'm going to paste this into SSMS and see what it says, but I suspect it's going to error since "account=1;" is not valid SQL as far as I can tell.
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.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Good question 🙂 Scary idea you've come up with! 😎 ... I don't want to think about number of places that might be vulnerable to that
-
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?
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
-
Since I have no ASP.NET experience, could someone please explain to me how this works?
I can imaginge that a textual result can be used for display in webpages, but what actually happens with a SQL Server result set? Is it automatically rendered in some kind of control?
Thanks in advance!
Best Regards,
Chris Büttner
-
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).
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
-
Thanks for this clarification, Tao!
Best Regards,
Chris Büttner
-
"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
-
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
-
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.
-
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
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply