ASP Search results page fails after upgrading DW to 8.02

[lesharrison]

This is DW 8.02, SQL server, ASP (not dot.net).

I have a search function that selects one of 5 column names and queries with user entered text. The old results page works fine until I replace the recordset code with the new parameter based recordsets in 8.02.

My search form uses GET and calls the results page with selColumn and txSearch variables.

Both the old and new results pages have this code to dimension the form variables:

<%
Dim rslocate__reqColumn
rslocate__reqColumn = "COMPANY"
If (Request.QueryString("selColumn") <> "") Then
rslocate__reqColumn = Request.QueryString("selColumn")
End If
%>
<%
Dim rslocate__reqSearch
rslocate__reqSearch = "test"
If (Request.QueryString("txSearch") <> "") Then
rslocate__reqSearch = Request.QueryString("txSearch")
End If
%>

I have also tried Request.Form with no success. The data type in SQL server is nvarchar for all data.

On the results.asp page the old recordset that still works is:

<%
Dim rslocate
Dim rslocate_numRows

Set rslocate = Server.CreateObject("ADODB.Recordset")
rslocate.ActiveConnection = MM_members_STRING
rslocate.Source = "SELECT * FROM ncmortgage.members WHERE " + Replace(rslocate__reqColumn, "'", "''") + " LIKE '%" + Replace(rslocate__reqSearch, "'", "''") + "%' ORDER BY UserID ASC"
rslocate.CursorType = 0
rslocate.CursorLocation = 2
rslocate.LockType = 1
rslocate.Open()

rslocate_numRows = 0
%>


On the new 8.02 results.asp page that shows no errors but does not return any data the code is:

<%
Dim rslocate
Dim rslocate_cmd
Dim rslocate_numRows

Set rslocate_cmd = Server.CreateObject ("ADODB.Command")
rslocate_cmd.ActiveConnection = MM_members_STRING
rslocate_cmd.CommandText = "SELECT * FROM ncmortgage.members WHERE ? LIKE ? ORDER BY UserID ASC"
rslocate_cmd.Prepared = true
rslocate_cmd.Parameters.Append rslocate_cmd.CreateParameter("param1", 200, 1, 10, rslocate__reqColumn) ' adVarChar
rslocate_cmd.Parameters.Append rslocate_cmd.CreateParameter("param2", 200, 1, 35, "%" + rslocate__reqSearch + "%") ' adVarChar

Set rslocate = rslocate_cmd.Execute
rslocate_numRows = 0
%>

I have tried everything over the last two days to get this to work. All of the other functions that I have converted are working fine in 8.02 but this one eludes me. Any help is sincerely appreciated.

tks,
Les H.

[Newsgroup_User · Accepted solution]

> This is DW 8.02, SQL server, ASP (not dot.net).
>
> I have a search function that selects one of 5 column names and queries
> with
> user entered text. The old results page works fine until I replace the
> recordset code with the new parameter based recordsets in 8.02.




From
http://www.adobe.com/support/documentation/en/dreamweaver/dw8/releasenotes.html:
SQL injections in web applications
The update updates server-side code generated by Dreamweaver to protect
databases against SQL Injection.


Here's the workaround:
http://kb.adobe.com/selfservice/viewContent.do?externalId=kb402875&sliceId=2

[Newsgroup_User]

lesharrison wrote:
> I have tried everything over the last two days to get this to work. All of the
> other functions that I have converted are working fine in 8.02 but this one
> eludes me. Any help is sincerely appreciated.

The update stopped you from being able to use dynamic sql as its a
serious security threat. There are more and more sites getting planted
with virus' via sql injection, including some really big sites, like
aspfaq.com.

http://sqlblog.com/blogs/denis_gobo/archive/2008/06/25/7491.aspx

Its a big problem, as a rule of thumb, dynamic sql should not be used.
There are times it can be used, but only when everything has been
considered.

Steve

[Newsgroup_User]

Also take a look at this:
http://blogs.msdn.com/buckwoody/archive/2008/06/25/stopping-sql-injection-in-it-s-tracks.aspx

Its time we developers became security experts. This year over 500,000
sites have been infected!

Steve

[lesharrison]

I totally agree. Tools like Dreamweaver have allowed a lot of sites to be built with wide open holes....which is why I am rebuilding all of my sites. This particular site was hit a dozen times until I rebuilt every login and forms function.

[Newsgroup_User]

lesharrison wrote:
> I totally agree. Tools like Dreamweaver have allowed a lot of sites to be built
> with wide open holes....which is why I am rebuilding all of my sites. This
> particular site was hit a dozen times until I rebuilt every login and forms
> function.

At least Adobe fixed the main problem and tried to educate us at the
same time. The technote that was with the update explained what the
problem was and how to work around it.

Part of being a web developer is learning all the new tricks and
techniques, but also its also about staying in touch with what is
happening in the real world, and being able to respond to security
situations as they occur.

Many of us don't have an IT department with a security expert who is
clued up on everything, so we wear many hats. Its our responsibility to
maintain our knowledge to keep our clients sites safe and secure.

Most of time we get burnt once and never make the same mistake again,
its just a shame that it takes the burning to get our attention.

Steve

[Jonbon999]

Not if you correctly code and dont rely on dreamweaver