June 6, 2008 at 7:53 am
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.
June 6, 2008 at 7:55 am
Awesome question! Quite scary too... :Whistling:
June 6, 2008 at 7:56 am
Yah me to. Can't see it on the screen... gonna try the sleect trick.
June 6, 2008 at 8:03 am
GSquared (6/6/2008)
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.
June 6, 2008 at 8:11 am
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. 🙁 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!
June 6, 2008 at 8:14 am
Brad (6/6/2008)
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. 🙁 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.
Stored procedures have to be properly parameterized too. I've seen garbage like this way too many times:
string sql = "EXEC my_stored_proc " + param1;
Which still leaves you wide open per the author's example.
June 6, 2008 at 8:18 am
If the web page does not use stored procedures, but instead uses dynamic SQL,
Maybe this is also a good place to point out that stored procedures and dynamic SQL are not mutually exclusive. Too often has the advice to "replace dynamic SQL with stored procedures" resulted in stored procedures that boil down to
CREATE PROC DoSomething @Parm varchar(1000)
AS
EXEC (@Parm)
Okay, they are often slightly more complicated, but the point is that this still is dynamic SQL, and still incurs all the same vulnerabilities.
Further reading at http://www.sommarskog.se/dynamic_sql.html
June 6, 2008 at 8:19 am
Mike C (6/6/2008)
Stored procedures have to be properly parameterized too.
You posted this while I was composing my similar reply 🙂
Great minds think alike! 😀
June 6, 2008 at 8:21 am
Hugo Kornelis (6/6/2008)
Mike C (6/6/2008)
Stored procedures have to be properly parameterized too.You posted this while I was composing my similar reply 🙂
Great minds think alike! 😀
Before I write I always ask myself, "WWHD" (What Would Hugo Do)! 🙂
June 6, 2008 at 8:35 am
Brad (6/6/2008)
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. 🙁 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.
Even with proper parameters and procs in the database, if the front-end is built to use, for example, in VB.NET, SqlCommand can be horribly misused and allow exactly this situation. That's why I left out that part of 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
June 6, 2008 at 8:36 am
Stored procedures have to be properly parameterized too. I've seen garbage like this way too many times:
string sql = "EXEC my_stored_proc " + param1;
Which still leaves you wide open per the author's example.
Correct, I think I must have glazed over the
And runs that in a dynamic SQL command in SQL Server 2005?
when I was thinking about the Stored Procedure usage.
Although, correct me if I'm wrong when getting the Account ID SQL injection not be possible if you did the following.
Dim AccountID as Integer
AccountID = Request.QueryString("AccountID")
string sql = "EXEC my_stored_proc " + AccountID
June 6, 2008 at 8:40 am
Brad (6/6/2008)
Stored procedures have to be properly parameterized too. I've seen garbage like this way too many times:
string sql = "EXEC my_stored_proc " + param1;
Which still leaves you wide open per the author's example.
Correct, I think I must have glazed over the
And runs that in a dynamic SQL command in SQL Server 2005?
when I was thinking about the Stored Procedure usage.
Although, correct me if I'm wrong when getting the Account ID SQL injection not be possible if you did the following.
Dim AccountID as IntegerAccountID = Request.QueryString("AccountID")
string sql = "EXEC my_stored_proc " + AccountID
Actually the way it's written there's no & between the accountid=1 and the ;declare ... portion, so accountid is actually equal to 1;declare ...
*** Oops, missed the Integer variable declaration. Yes, this should fail. OTOH, if someone allows all of that to be typed into a numeric field in the UI can you really depend on them to properly type their data? 🙂 I would suspect that AccountID could very likely be declared as Object or String by this particular developer.
June 6, 2008 at 8:45 am
Right, hince why I was saying no SQL injection cause it'd fail the convert big long string to int!
Anyways good thought provoking question!
June 6, 2008 at 8:50 am
Brad (6/6/2008)
Stored procedures have to be properly parameterized too. I've seen garbage like this way too many times:
string sql = "EXEC my_stored_proc " + param1;
Which still leaves you wide open per the author's example.
Correct, I think I must have glazed over the
And runs that in a dynamic SQL command in SQL Server 2005?
when I was thinking about the Stored Procedure usage.
Although, correct me if I'm wrong when getting the Account ID SQL injection not be possible if you did the following.
Dim AccountID as IntegerAccountID = Request.QueryString("AccountID")
string sql = "EXEC my_stored_proc " + AccountID
As far as I can see, this will submit the following code to your server (formatted for readability; the carraige returns are not in the actual code, but they are ignoerd by SQL Server anyway):
EXEC my_stored_procedure @account=1;
declare @a varchar(1000);
set @a=cast(0x73656C656374206E616D652066726F6D207379732E6461746162617365733B
as varchar(1000));
exec(@a)
And the result will be that first my_stored_proc is executed with a parameter value of 1 for @account (note that I added the @ to correct a syntaxa error), and then a query is executed that lists all databases on your server. Or does worse things, with a different hexadecimal string..... :crying:
Edit: Oops, missed the Dim AccountID as Integer Silly me :rolleyes:
June 6, 2008 at 8:57 am
The wording of the question was awkward, no doubt about it. Sorry about that.
Whoa - the whole question went shooting right over my head since first reading. No need to apologize. Though it feels like getting hit by a train ... didn't know about any of this, it puts me on a better track for security awareness. If it sounds like a dig at your question from my end, my question about "THIS" was more just an expression of consternation.
Please ask more like it - while I don't enjoy being confounded, this question should get an "A+" for "bringing web security and sql injection to light". And I truly am interested in how you can post a slide bar on the web in a question in that manner. I don't see it there in the IFCode Shortcuts there to the side of this page (http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=dyjn2e451xk4lzbbizfftofj)
Thanks for the great question!!!
Jamie
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply