 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / July 2008Need help with SQL injection proof recordset Update code
Hi All, Our website (developed by a 3rd party) was recently hit by an Sql injection attack and I have been going through and hardening the code. It uses asp and talks to a sql server 2000 backend for a bunch of the page content. I have been switching over to using parameterized queries by creating Command Objects etc, and this has been straightforward for the most part, but I have a piece of code that uses a recordset Update command to take some form info and put it into the database. I am struggling to find a solution for this. The code is of the following form: Set cConn = getDatabaseConn() Set cRS = Server.CreateObject("ADODB.RecordSet") cRS.Open "PageContent",cConn,2,2 cRS.AddNew cRS("PageName") = request.form("itemName") cRS("Category") = request.form("category") cRS("Content") = Request.Form("pageBody") cRS.Update cRS.Close closeDatabaseConn(cConn) If this was a Sql statement like "Update Table Set Field = ?, where id =?", I would construct a parameterized query with a command object. Is there a way of doing this with a recordset update command? Thanks for any assistance. > Hi All, > [quoted text clipped - 27 lines] > > Is there a way of doing this with a recordset update command? This parameterization should already be occurring behind the scenes. You can confirm by using SQL Profiler to run a trace, but I'm pretty sure that a recordset update will not be prone to sql injection.  SignatureMicrosoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. > This parameterization should already be occurring behind the scenes. You > can confirm by using SQL Profiler to run a trace, but I'm pretty sure > that a recordset update will not be prone to sql injection. Thanks Bob! I didnt know that. It also explains why msscasi_asp.exe didnt flag this as a potential problem. EdG wrote on Wed, 16 Jul 2008 09:40:18 -0700: > Hi All, > Our website (developed by a 3rd party) was recently hit by an Sql > injection attack and I have been going through and hardening the code. > It uses asp and talks to a sql server 2000 backend for a bunch of the > page content. > I have been switching over to using parameterized queries by creating > Command Objects etc, and this has been straightforward for the most > part, but > I have a piece of code that uses a recordset Update command to take > some form info and put it into the database. I am struggling to find a > solution for this. > The code is of the following form: > Set cConn = getDatabaseConn() > Set cRS = Server.CreateObject("ADODB.RecordSet") > cRS.Open "PageContent",cConn,2,2 cRS.AddNew cRS("PageName") = > request.form("itemName") > cRS("Category") = request.form("category") > cRS("Content") = Request.Form("pageBody") > cRS.Update cRS.Close closeDatabaseConn(cConn) > If this was a Sql statement like "Update Table Set Field = ?, where id > =?", > I would construct a parameterized query with a command object. > Is there a way of doing this with a recordset update command? > Thanks for any assistance. This is still bad as you're not validating the form values - while it's not SQL injection, you are vulnerable to Cross Site Scripting (XSS) if you later output those database fields to a browser without stripping out HTML tags; for instance, if the "pageBody" value contained an IFRAME or a SCRIPT tag that pulled malware from another site then by not checking it before it goes, or on the way out to the browser, you are leaving visitors at risk of being infected when visiting your pages. SignatureDan As others have said, if you take a look with the SQL-Server Profiler, you will see that ADO use parameterized queries to execute these commands. Practically, the only way that you can be hurt by SQL injection attack is when you are dynamically building your one sql strings. For alphanumeric string values - that are to be enclosed between single quotes - all you have to do is to replace any enclosed single quote with two single quotes by using the replace command. Very easy to do. Of course, if you are using double quotes as the string delimiter then these are those that you must double using the replace command. The problem is with numeric values because they must not be enclosed between single quotes but the values taken from an ASP page might contain anything. The easiest way of dealing with this would be to simply parse (store) any numeric value from its string value into a variable before using it. Don't forget that values already stored in a table can still be effective in doing an injection attack; so you must always take these previous precautions when building any dynamic sql string even when dealing with values coming from a table. Finally, as another poster has suggested, you or your users might also be hit with cross scripting when dealing with HTML. The easiest way of dealing with this is to simply use the Server.HTMLencode () method with everything that you write to an HTML page. This way, whatever an hacker try to put in a page, it will always be simply displayed on the page by the browser and never get executed. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) > Hi All, > [quoted text clipped - 33 lines] > > Thanks for any assistance. > As others have said, if you take a look with the SQL-Server Profiler, > you will see that ADO use parameterized queries to execute these [quoted text clipped - 7 lines] > delimiter then these are those that you must double using the replace > command. This is not enough. There are several documented techniques for hackers to use to get around this simplistic strategy. SQL can be injected without a single quote character involved. In fact, the recent worm attack that hit so many websites in the last month used one of those techniques. If parameters are possible, forget attempting to excape strings: use parameters. If dynamic sql is necessary, validate data against a list of acceptable entries, rather than attempting to outthink the bad guys. SignatureMicrosoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" As far as know, the recent work attack used a standard method for injecting javascript into an HTML page via an IFrame and was easily defeated with the simple use of the Server.HTMLEncode method() and to my knowledge, there is no way that the simple method of doubling all possible single quotes inside an alphanumeric value can be circumvented by the hackers; with maybe the possibility of truncation if the buffer used for the replace command is too small. (In fact, the recent work attack is not an SQL-Injection per see; it's an HTML injection attack.). Of course, we must also not forget about the possibility of numerical *string* values that might contains something else than a simple number but as I said, by first parsing them into a numerical variable; we get clear of this possible loophole. However, I agree with you that parameters should always be used whenever possible and that in most case, taking the decision of simply dropping anything containing binary data, escape sequences and comment characters would be wise. Some people will parse these things out; in my opinion, dropping the whole thing would be even better. Here are two more reference for the OP: http://msdn.microsoft.com/en-us/library/ms161953.aspx http://msdn.microsoft.com/en-us/library/ms998271.aspx SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) >> As others have said, if you take a look with the SQL-Server Profiler, >> you will see that ADO use parameterized queries to execute these [quoted text clipped - 15 lines] > parameters. If dynamic sql is necessary, validate data against a list of > acceptable entries, rather than attempting to outthink the bad guys. > As far as know, the recent work attack used a standard method for > injecting javascript into an HTML page via an IFrame and was easily > defeated with the simple use of the Server.HTMLEncode method() No, that was the second stage of the two-stage attack; in the first stage the bot used sql injection to declare and open a cursor to update every "string" field in the database, appending its js payload to the current contents: http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql-injection-i ncident-part-2-meat.aspx SignatureMicrosoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" First, thanks for the info. Second, even in this case, the sql portion of this attack would have been easily defeated by using the replace method for the embedded single quotes. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) >> As far as know, the recent work attack used a standard method for >> injecting javascript into an HTML page via an IFrame and was easily [quoted text clipped - 5 lines] > contents: > http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql-injection-i ncident-part-2-meat.aspx Notice that there are 2 versions - one that starts with a single quote, and one that does not. The latter form relied on using a numeric value that was being passed into a SQL string without validation. It was easily defeated by simply checking the value is a number before using it, or converting the querystring value to a number (eg using CInt or CLng) prior to using in dynamic SQL, but took advantage of a lot of programmers having put no validation checks into their code. While the replace with single quotes works for one version, it doesn't for the other. Dan Sylvain wrote on Wed, 30 Jul 2008 12:33:12 -0400: > First, thanks for the info. Second, even in this case, the sql portion > of this attack would have been easily defeated by using the replace > method for the embedded single quotes. >>> As far as know, the recent work attack used a standard method for >>> injecting javascript into an HTML page via an IFrame and was easily >>> defeated with the simple use of the Server.HTMLEncode method() >> No, that was the second stage of the two-stage attack; in the first >> stage the bot used sql injection to declare and open a cursor to >> update every "string" field in the database, appending its js >> payload to the current contents: >> http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql- >> injection-incident-part-2-meat.aspx Bob Barrows [MVP] wrote on 30 jul 2008 in microsoft.public.inetserver.asp.db: >> As others have said, if you take a look with the SQL-Server Profiler, >> you will see that ADO use parameterized queries to execute these [quoted text clipped - 16 lines] > validate data against a list of acceptable entries, rather than > attempting to outthink the bad guys. As first "line" of defence, let us all change the probably still common: sql = "... WHERE id = " & request.querystring("id") to sql = "... WHERE id = " & CInt(request.querystring("id")) or even better, since it will not show the sql in the errorline,if an errorline is still shown: temp = CInt(request.querystring("id")) sql = "... WHERE id = " & temp and search our ancient, be it still in use, pages for those instances. SignatureEvertjan. The Netherlands. (Please change the x'es to dots in my emailaddress) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.