SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Need help with search page

    hello, i've created a page where pple can search for pics by their names, all my photos are stored in DB under the coluum names (FileName, FileName2, FileName3 and FileName4)
    i want to make it search in all those coluums for the given name, but for some reason i can only look in 1 at a time

    this is a part of the searching page
    Code:
    	mySQL = "SELECT * FROM Animals "
    
    	mySQL = mySQL & "WHERE FileName LIKE"
    	mySQL = mySQL & "'%"
    	mySQL = mySQL & Request.QueryString("picname")
    	mySQL = mySQL & "%'"
    I've tried doing something like mySQL = mySQL & "WHERE FileName, FileName2, FileName3, FileName4 LIKE" but it didnt allow me to...

    Thanks in advance for the help

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    That's because the format of the query is incorrect. You would need to do something like this. May I also suggest you tighten your security and not execute a query directly off a user input? This post gives you a decent start, but parameterized queries are even better.

    (I apologize for any syntax typos, but I've been working in other languages recently....)

    Code ASP:
    Set conn = Server.CreateObject("ADODB.Connection")
     conn.Open "connection string goes here...."
    Set cmdTemp = Server.CreateObject("ADODB.Command")
     cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = conn
     
    cmdTemp.CommandText = "SELECT * FROM Animals " & _
            "WHERE FileName LIKE ?" & _
              " OR FileName2 LIKE ?" & _
              " OR FileName3 LIKE ?" & _
              " OR FileName4 LIKE ?" & _
    ' Loop is just here to remove duplicated code....
    for x = 0 to 3       
     cmdTemp.Parameters(x) = "%" & Request.QueryString("picname") & "%"
    next
    On Error Resume Next 
    Set rs = Server.CreateObject("ADODB.Recordset")
     rs.Open cmdTemp
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help but there's a problem with the 'for x=0 to 3' part, throws a syntax error, and since i dont really undertand whats that for (since im still a learner about ASP) i need help with how to fix that

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Ooops. Remove the & _ from the end of the filename4 line (the & _ tells the interpreter to continue the command onto the next line)

    Like I said, it's been a while....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    heh thats ok thats solved the problem, but now after i put some name to search for it throws an error again

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E51)
    Provider cannot derive parameter information and SetParameterInfo has not been called.

    this line:
    Code:
    cmdTemp.Parameters(x) = "%" & Request.QueryString("picname") & "%"

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Do you have the correct number of question marks in the query OR is the querystring value named something else? It looks as if there's either a problem with the number of parameters you're trying to pass or your input is not named what you expected - are you perhaps using a method="POST" in your form instead of method="GET" or else not passing the searchvalue in the querystring?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    uhm, i do use method "get" and the searchvalue does passing into the querystring
    i didnt quite understand which question marks do u mean?
    Those are the only things i got in my pages

    searchbypic.asp
    Code:
    <form action="searchbypic2.asp" method="get">
               <br /><br />
               <input type="text" name="picname" />
            שם התמונה
            <br />
            <input type="submit" value="חפש" />
          </form>
    searchbypic2.asp (thats what u gave me with a few changes i had to do
    Code:
     Set conn = Server.CreateObject("ADODB.Connection") 
        conn.Open "Driver={Microsoft Access Driver (*.mdb)};" &_
    	      	   "Dbq=" & Server.MapPath("database.mdb") & ";" 
        
    Set cmdTemp = Server.CreateObject("ADODB.Command") 
    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = conn 
    cmdTemp.CommandText = "SELECT * FROM Animals " & _        
    "WHERE FileName LIKE ?" & _         
     " OR FileName2 LIKE ?" & _         
      " OR FileName3 LIKE ?" & _          
      " OR FileName4 LIKE ?"
    
      for x = 0 to 3        
    cmdTemp.Parameters(x) = "%" & Request.QueryString("picname") & "%"
      next
      On Error Resume Next 
      Set rs = Server.CreateObject("ADODB.Recordset") 
      rs.Open cmdTemp
    Thats about it, all the rest of the page isnt related to the uploading part...so the problem should be at that part of searchbypic2.asp...

    Thanks for the help

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Sorry I've taken so long to get back to you - I'm stumped and have been trying to do some investigation. I can't find anything which should cause that error (the code I gave you is what I use all the time).

    A search suggested using the other method for parameterizing your query (this is a quick & dirty, so I would expect a typo...this is to give you an example of the format)

    Code ASP:
    dim picname
    picname = "%" & Trim(Request.QueryString("picname")) & "%"
    for x = 0 to 3
         objCmd.Parameters.Append objCmd.CreateParameter("filename" & x, adVarChar, len(picName), picname)
    next
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hello,
    thanks for the help but now i dont know where to place that code at all, i've tried placing it in some possible places and it still throws errors, also as it seems, it just checks if the picname is equal to filename (what about filename2, filename3, filename4?)

    Still need to throw some light over here Thanks in advance

  10. #10
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I think you'll need to post the whole script (please change the db connection information - we'll still need the code there for line number verification) for us to figure it out further. We'll also need an exact error message (which includes the line number).

    The other thing to check is to place the query in it's raw format into a SQL query window and verify the data is what you think it is.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, sorry for not updating u i've got it working, searched around abit and found the solution, for those who are intrested :

    Code:
    	mySQL = "SELECT * FROM Animals "
    	mySQL = mySQL & "WHERE FileName LIKE"
    	mySQL = mySQL & "'%"
    	mySQL = mySQL & Request.QueryString("picname")
    	mySQL = mySQL & "%'"
         mySQL = mySQL & "OR"
    	mySQL = mySQL & " FileName2 LIKE"
        mySQL = mySQL & "'%"
    	mySQL = mySQL & Request.QueryString("picname")
    	mySQL = mySQL & "%'"
             mySQL = mySQL & "OR"
    	mySQL = mySQL & " FileName3 LIKE"
        mySQL = mySQL & "'%"
    	mySQL = mySQL & Request.QueryString("picname")
    	mySQL = mySQL & "%'"
             mySQL = mySQL & "OR"
    	mySQL = mySQL & " FileName4 LIKE"
        mySQL = mySQL & "'%"
    	mySQL = mySQL & Request.QueryString("picname")
    	mySQL = mySQL & "%'"
    Thats pretty long but sitll works excactly how i wanted it

    Thanks alot for ur help Dave, really appreciated.

  12. #12
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ulthane View Post
    Oh, sorry for not updating u i've got it working, searched around abit and found the solution, for those who are intrested :

    : <snip />

    Thats pretty long but sitll works excactly how i wanted it

    Thanks alot for ur help Dave, really appreciated.
    I'm glad you got it working, but if you're going to do it that way, you REALLY need to visit the link I posted in post #2 of this thread and secure your values - you are a prime target for SQL injection attacks with no validation on your querystrings....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  13. #13
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, people have told me about security stuff over this forum a few times i will look into those links and learn about security, but that will have to wait until i will finish with the "meat" of the website at least

  14. #14
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I would suggest that getting it right in the first place (regarding security) will be quicker and easier than doing the "meat" then trying to retrofit the security afterwards.
    Ian Anderson
    www.siteguru.co.uk

  15. #15
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    well another reason why i haven't started it yet is that im afraid it would screw up my site again , i had to re-write that site over and over because of langauge issues everytime i tried to add something new to the site.

    and since this is going to be my first time working with security stuff ill need a hand, can u give me a few links for beginners i can look at?

    Thanks in advance...

  16. #16
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The SP post that Dave links in reply #2 would be helpful. In your case you could use it like so ...

    Code ASP:
    Public Function CleanQuotes( ByVal strDirty, boolRemove )
    	strData = Replace( Replace( strDirty, Chr(147), Chr(34) ), Chr(148), Chr(34) )
    	strData = Replace( Replace( strData, Chr(146), Chr(39) ), Chr(96), Chr(39) )
    	If Not CBool( boolRemove ) Then
    		strData = Replace( strData, Chr(39), String( 2, Chr(39) ) )
    		strData = Replace( strData, Chr(34), Chr(39) & Chr(34) )
    	ElseIf CBool( boolRemove ) Then
    		strData = Replace( strData, Chr(39), Chr(32) )
    		strData = Replace( strData, Chr(34), Chr(32) )
    		Do While InStr( 1, strData, String( 2, Chr(32) ) ) > 0
    			strData = Replace( strData, String( 2, Chr(32) ), Chr(32) )
    		Loop
    	End If
    	CleanQuotes = Trim( strData )
    End Function
     
    picname = CleanQuotes (Request.QueryString("picname"), False)
     
    mySQL = "SELECT * FROM Animals "
    mySQL = mySQL & "WHERE FileName LIKE '%" & picname & "%' "
    mySQL = mySQL & "OR FileName2 LIKE '%" & picname & "%' "
    mySQL = mySQL & "OR FileName3 LIKE '%" & picname & "%' "
    mySQL = mySQL & "OR FileName4 LIKE '%" & picname & "%'"

    Or simply Google it.
    Ian Anderson
    www.siteguru.co.uk

  17. #17
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks, i've added it to my page
    Also added the following code to my Admin's login page:
    Code:
    <&#37; 
    'Declare variables 
    Dim sUsername, sPassword
    'retrieve our form textbox values and assign to variables 
    sUsername=Request.Form("txtUsername")
    sPassword=Request.Form("txtPassword")
    
    'Call the function IllegalChars to check for illegal characters
    If IllegalChars(sUsername)=True OR IllegalChars(sPassword)=True Then
    Response.redirect("no_access.asp")
    End If 
    
    'Function IllegalChars to guard against SQL injection
    Function IllegalChars(sInput) 
    'Declare variables 
    Dim sBadChars, iCounter 
    'Set IllegalChars to False 
    IllegalChars=False
    'Create an array of illegal characters and words 
    sBadChars=array("select", "drop", ";", "--", "insert", "delete", "xp_", _
    "#", "%", "&", "'", "(", ")", "/", "\", ":", ";", "<", ">", "=", "[", "]", "?", "`", "|", "declare", "convert") 
    'Loop through array sBadChars using our counter & UBound function
    For iCounter = 0 to uBound(sBadChars) 
    'Use Function Instr to check presence of illegal character in our variable
    If Instr(sInput,sBadChars(iCounter))>0 Then
    IllegalChars=True
    End If
    Next 
    End function
    %>
    btw when u say "sql injection" what does that means excactly? pple trying to hack my site or does it mean that pple have the ability to spam my site with useless things? because all the pages i've been working on can be accessed only by admins. each page checks if the person really is an admin and only then let him in:

    Code:
          <%						
       	if session ("OkToEnter") <> "OKmanager" then
       	   
    		Response.Redirect "NoManager.html"
    		
    	end if	
    
            session ("kind")="manager"
       %>
    the only thing that can be accessed by every1 is uploading photos to the gallery, do i need to add anything else or im fine so far?

  18. #18
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SQL Injection relates to people being able to type stuff into a form field in such a way that they can hack your database. If you use user-supplied form data without doing any checks on it (e.g. a file upload form that also has a text field for the photo description) then a hacker could use the text field to type in some code to hijack your SQL command.

    There is lots of information online about SQL Injection.
    Ian Anderson
    www.siteguru.co.uk

  19. #19
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    And if i dont have any form that can be accessed by a normal user? like i've said only logged in managers can use all those pages, do i still need all of those security stuff? (i dont belive a manager would hack his own server )

    The only page can be accessed by any1 is the upload pic to gallery page, and it doesn't have anything besides of a single file upload.

    and last question, is there such a thing as database password? if yes, does it even needed?

    Thanks in advance

  20. #20
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If the general access page ONLY allows a single file upload (no text entry) then there is less to worry about. However I would still err on the side of caution and include form checking even on the admin pages - what if someone hacks the admin login/guesses the details?

    Yes you can have a password on the database (if it is a file database like Access). However in your case it would make no difference - it would only make things a little more difficult for somebody who downloaded the database file; your ASP code would need to include the database password anyway, so this couldn't be used as a substitute for form checking.
    Ian Anderson
    www.siteguru.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •