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 Thursday, June 5, 2008 9:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #512644
Posted Thursday, June 5, 2008 10:58 PM


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
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.
Post #512678
Posted Thursday, June 5, 2008 11:00 PM


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
Hmmmm. This is what I get in SSMS:

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='."
Post #512679
Posted Thursday, June 5, 2008 11:25 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:03 AM
Points: 488, Visits: 403
My years of reading hex as an assembler/C programmer has finally paid off in SQL!
Post #512685
Posted Friday, June 6, 2008 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,916, Visits: 8,164
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 :D



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #512730
Posted Friday, June 6, 2008 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,916, Visits: 8,164
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #512734
Posted Friday, June 6, 2008 2:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 5, 2014 6:18 AM
Points: 1,140, Visits: 326
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
Post #512743
Posted Friday, June 6, 2008 4:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 25, 2014 10:35 AM
Points: 1,385, Visits: 1,241
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.
Post #512774
Posted Friday, June 6, 2008 5:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #512793
Posted Friday, June 6, 2008 5:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 25, 2014 10:35 AM
Points: 1,385, Visits: 1,241
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.
Post #512804
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse