| Author |
 Topic Search  Topic Options
|
Mart 
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Search help
Posted: 29 April 2003 at 9:51am |
Hi im making a search engine for MP3s and this is my SQL code:
strSQL = "SELECT tblMP3.Artist, tblMP3.Track, tblMP3.Genre, tblMP3.Comments, tblMP3.URL, tblMP3.autonum FROM tblMP3 WHERE genre LIKE '%" & request.querystring("cat") & "%' ORDER BY tblMP3.Artist;"
But I need it so if they just type the track it will also come up how do I do that?
Thanks, Martin.
|
 |
MorningZ
Senior Member
Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 10:23am |
Well, do you have two different search boxes (for each type)?
Or do you have some sort of selection of "what kind of search" (and do you allow "one", "the other", "both", or "one or the other"?
Or do you want to search both columns with one entered criteria (opens up doors to be very very taxing on the server and slow)?
Speaking of opening up doors, whenever you take user entered data and build a SQL statement, do some sort of check that they don't enter a single tic mark, because that above SQL string build is wide open to someone getting major access on your Database (search google for "SQL Injection" if you want more info on that)
|
|
Contribute to the working anarchy we fondly call the Internet
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 11:11am |
Ok I have one search box which they can enter either a artist or a track or both also it has got to allow artist - track.
Thanks, Martin.
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 11:31am |
strSQL = "SELECT tblMP3.Artist, tblMP3.Track, tblMP3.Genre, tblMP3.Comments, tblMP3.URL, tblMP3.autonum FROM tblMP3 WHERE genre LIKE '%" & strSearchbox & "%' OR Track LIKE '%" & strSearchbox & "%' ORDER BY tblMP3.Artist;"
I took the Request.Querystring out as I agree with Z that you should filter that entry first, thus this way it searches in both fields...
|
|
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 11:57am |
Ok I've done that now I have another problem , in the database a lot of URLs have my old IP 80.4.220.189 and I have changed services since(I have a new IP).
Is there any way of changing all 80.4.220.189 IP addresses to **.*.***.*** in the tblMP3.URL?
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 12:28pm |
Thanks for the SQL injection tip, is it just single quotes ' I have to replace with double quotes ''?
Thanks, Martin.
|
|
MorningZ
Senior Member
Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 12:39pm |
For strings, replace single quotes with two single quotes
ex/
"thisCol LIKE '%" & Replace(strSearch,"'","''") & "%'"
For numbers, like say you are passing an ID across the querystring, I have always used the following method:
Function Clng0( i_str ) 'Common function in your code
Clng0 = 0
On Error Resume Next
Clng0 = Clng( i_str )
On Error Goto 0
End Function
To use it, and you will get rid of any "funny business":
SearchThisID = Clng0( Request.QueryString("PassedID") )
|
|
Contribute to the working anarchy we fondly call the Internet
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 April 2003 at 12:58pm |
Thanks MorningZ, the replace works but you numeric one always gives a null value, Am i doing it right?
Function Clng0( i_str ) 'Common function in your code
Clng0 = 0
On Error Resume Next
Clng0 = Clng( i_str )
On Error Goto 0
End Function
SQL = "FROM somewhere WHERE data LIKE '%" & clng0(request.querystring("ID")) & "%';"
|
|
