Database Search in my ASP search page. 1

[WB786]

I have everything working BUT my problem is that if I am searching for a combo of words and it doesn't work.

Here is the coding:

Set oRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblGroups WHERE [Active]=true and [GroupName] like '%" & Request.Form("SearchValue") & "%' ORDER BY [GroupNumber]"
oRS.CursorType = 2
oRS.LockType = 3
oRs.Open strSQL, oConn
Do while not oRs.EOF

I can search on say Air and it pulls up all items that start with the word AIR. But lets says I type in: Carpet Cleaner - it doesn't - because in GroupName it is really listed as "Carpet Hot Water Cleaner".

I want it to be able to search on all keywords instead.

Thanks,

WB

 

[HowardMarks]

You need to split the search term up into separate terms and then test for all or 1 of those terms in your sql, e.g.

Dim searchArray : searchArray = Split(Request.Form("SearchValue")," ")

strSQL = "SELECT * FROM tblGroups WHERE [Active]=true AND ("

Dim searchItem
For searchItem = 0 to uBound(searchArray)

  strSQL = strSQL "[GroupName] like '%" & searchArray(searchItem) & "%' "

  If Not searchItem = searchArray.RecordCount Then
    'Change AND to OR to match against any search word rather than all words
    strSQL = strSQL & " AND "
  End If

Next

strSQL = strSQL & ") ORDER BY [GroupNumber]"

This might need a bit of tweaking to get it working properly, e.g. checking for the existence of a current array item, but the underlying logic should be sound.


Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[WB786]

The error I am getting is:

Expected end of statement

/search_result.asp, line 66

strSQL = strSQL "[GroupName] like '%" & searchArray(searchItem) & "%'"

Thanks for the help!

WB

 

[monksnake]

make this

strSQL = strSQL "[GroupName] like '%" & searchArray(searchItem) & "%'"

This

strSQL = strSQL [!]&[/!] "[GroupName] like '%" & searchArray(searchItem) & "%'"

<.

 

[WB786]

That worked! Now I have another problem. See Below. Sorry I am new to searchArray function.

<% 
			  		Set oRS  = Server.CreateObject("ADODB.Recordset")			  
					Dim searchArray : searchArray = Split(Request.Form("SearchValue")," ")
		
					strSQL = "SELECT * FROM tblGroups WHERE [Active]=true AND ("
					Dim searchItem
					For searchItem = 0 to uBound(searchArray)
					
					strSQL = strSQL & "[GroupName] like '%" & searchArray(searchItem) & "%'"
					  					
				[COLOR=red]OBJECT REQUIRED--->[/color] If Not searchItem = searchArray.RecordCount Then
							'Change AND to OR to match against any search word rather than all words
							strSQL = strSQL & " AND "
						  End If					
					Next

					strSQL = strSQL & ") ORDER BY [GroupNumber]'"
		oRS.CursorType = 2
		oRS.LockType = 3
		oRs.Open strSQL, oConn
		Do while not oRs.EOF

						Response.Write"<table cellpadding=0 cellspacing=1 border=1 bordercolor=#FFFFFF bgcolor=#EAEAEA width='100%'>"
							Response.Write"<tr>" 
									Response.Write"<td width=120 height=120 align=center>"
									if oRs("GroupImage")="none" Then
										Response.Write"<img src='images/noimage.gif'>"
									else
										Response.Write oRs("GroupImage")
										Response.Write"<br><center><font size='-2'> Click Image To Enlarge<center>"
									end if
									Response.Write"</td>"
									Response.Write"<td width=120 align=center>"
									Response.Write "Item Number<br>"
									Response.Write oRs("GroupNumber")
									Response.Write"</td>"
									Response.Write"<td width=200 align=center>"
									Response.Write oRs("GroupName")
									'Response.Write"<br>"
									Response.Write"</td>" 
									Response.Write"<td width=350 align=LEFT>"
									Response.Write oRs("GroupDesc")
									'Response.Write"<br>"
									Response.Write"</td>" 
							Response.Write"</tr>" 
			oRs.MoveNext 
		loop
		oRS.Close
		Set oRS = Nothing	
		oConn.Close
		set oConn = Nothing
						Response.Write"</table>"	
	%>

Thanks for the hand holding a bit.


WB

 

[HowardMarks]

Oops sorry i should have made that line

If Not searchItem = uBound(searchArray)

rather than searchArray.RecordCount.

Too used to using recordsets!

Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[WB786]

Howard,

THAT WORKED!!! AWSOME!!!

THANKS AGAIN!!!!!

PS: My company website that I setup a long time ago was missing this search feature. You can see the website here:

http://www.capitalrentals.com/equipment.asp

WB

 

[HowardMarks]

My pleasure, i'm glad you got it sorted. Website looks very nice btw.



Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[onpnt]

That is until you enter this to search by

This isn't sql injection proof

Then after I see that I get your table anmes, server names, network information, usernames/passwords and huh oh.....

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[WB786]

onpnt,

You have a valid point. But in my case:
1) It is hosted on 1and1.com.
2) the DB is an Access Database - no SQL involved here.
3)The above code gets the job done - the hackers can steal all the info on equipment rentals they want and there are no usernames or passwords in the db.

If you would like to share your SQL hack proof code then this way others can see/use it.

Thanks,





WB

 

[onpnt]

None of this

   1) It is hosted on 1and1.com. 
   2) the DB is an Access Database - no SQL involved here.
   3)The above code gets the job done - the hackers can steal all the info on equipment rentals they want and there

Is relavant or just about any excuses are relavant to protecting against sql injection. Besides, it's not just protection. It's about writing error free code to. The most basic thing you should always do when accepting a string from the UI is protect against an unterminated string condition.

So in that and answering "If you would like to share your SQL hack proof code "

Replace single apostrophes with double apostrophes.

Replace(val,"'","''")

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[WB786]

onpnt,

"Error Free Code" - I like that. Ask Microsoft if their code is Error Free - LOL. Sorry I don't believe anyone can be perfect. We can all strive towards perfection.

Anyways,

Ok little confused here. you are saying this about the following:

Split(Request.Form("SearchValue")," ")

There are two Quote Marks and not Apostrophes as you state.

Should it really be like this instead:

Split(Request.Form("SearchValue"),"'",""")
Split(Request.Form("SearchValue"),Quote'Quote, QuoteQuoteQuote)

But see you then get an error: Unterminated string constant. That's because Apostrophie can be used to comment a line out in VBScript.

I am not a programmer so not sure about all the ins and outs of coding.

Thanks,

WB

 

[onpnt]

>> Ask Microsoft if their code is Error Free

Don't go there. We could go on for days on allt he errors that come out of Sun or IBM etc... MS is doing a good job out of the box.

I wasn't referencing the split at all

The request object is the value that is coming in. Do the work on it

something like
Split(Replace(Request.Form("SearchValue"),"'","''")," ")

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[WB786]

onpnt,

Thanks for all the insight here. I love picking on MS because that is all I use here. It is a love-hate thing I have with them. Don't take it personal.

Your idea is that if someone tries to submit a "blank value" then they can see your stuff. But wouldn't a form validation take care of blank inputs (which I do have, now)?

Thanks again,

WB

 

[ca8msm]

It's nothing to do with "blank values" as no-one mentioned them - SQL Injection


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[onpnt]

If you didn't take my advice you better hope some guy about to get fired from your place of employment doesn't see this

http://uk.youtube.com/watch?v=MJNJjh4jORY

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[WB786]

onpnt,

I am the only IT guy here. I do everything here. LOL.

Unlike the example in your video our website has no personal data. If someone wants to know the spec for a Bobcat then they can hack all they want.

Anyways, I did everything you said.

I also added form validation so the user has to put in something before pressing the search button (it was erroring out if you didn't put anything - making the code error proof you know).

Thanks for all the help!!


WB

 

[gmmastros]

If someone wants to know the spec for a Bobcat then they can hack all they want.

Understood! Sometimes you just don't care whether or not someone sees the data in the database. Depending on the nature of the data, this is perfectly acceptable.

Unfortunately, SQL Injection doesn't stop there. How would you feel if someone was able to add new records to the database? How about updating/changing the data? Or... How would you feel if someone were to delete all the data from your database?

Protecting yourself from SQL Injection is more than just preventing people from seeing it. Some hackers may be a bit more malicious than that.

-George

"the screen with the little boxes in the window." - Moron