CODEWITHSUNDEEP
sqlsearchasp-classic
chap
chap

Reputation: 1869

Why is this simple classic ASP search query not working?

I am making a very simple search query in classic asp that looks for words in the database that are 'like' the users search query.

My webpage is saying there are no results when I search for "test". However I have a specific post titled search which I can see in my database.

I'm not sure why this isn't working.

<% option explicit %>

<!DOCTYPE HTML>
<html>
<head>
  <link href="normalize.css" rel="stylesheet" type="text/css">
</head>
<body>
<!--#include file="header.asp"-->
<!--#include file="dbconn.asp"-->
<%
  dim stage, s, sql, info
  stage = request.querystring("stage")
  if stage = "" then stage=1



  '------------------------------------------------------------------
  if stage = 1 then
  '------------------------------------------------------------------

    response.write "<form action=""search.asp"" method=""get"">" &_
                   "<input type=""hidden"" name=""stage"" value=""2"">"&_
                   "<input type=""text"" id=""search"" name=""search"">" &_
                   "<input type=""submit"" value=""Search"">" &_
                   "</form>" 


  '------------------------------------------------------------------
  elseif stage = 2 then
  '------------------------------------------------------------------

    '--- grab the data from the form
    dim search
    search = Request.QueryString("search")



    '--- execute the query
    '                0       1            2
     SQL = " select ID, projectName, Description from projectstable"&_ 
            " where (projectName like ' %search% ' or description like ' %search% ')"

    set info=conn.execute(sql)

     if info.eof then
      response.write "    <div class=""box2"">"&chr(13)
      response.write "      Sorry, no records matching your query"&chr(13)
      response.write "    </div>"&chr(13)
    else
      response.write "<div class=""list"">" &_
                     "<table>" &_
                     "<tr>" &_
                     "<th>Title</th><th>Post</th>" &_
                     "</tr>"
      do
        response.write "<tr>" &_
                       "<td>"&info(1)&"</td><td>"&info(2)&"</td>"&_
                       "</tr>"
        info.movenext
      loop until info.eof
      response.write "</table></div>"
    end if

  '------------------------------------------------------------------
  end if  ' stage
  '------------------------------------------------------------------

  response.write "<br clear=""left""><br>"
  if stage=2 then 
    response.write "<i>that's all folks!</i><br><br>"&_
                   "<a href=""search.asp"">Search again</a> | " 

  end if
  response.write "<a href=""./"">back to main page</a>"
  conn.close
%>
</div>
</body>
</html>

Upvotes: 0

Views: 1099

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Your query needs to be changed to enclose the search variable into the sql string (vs searching for the phrase "search"):

SQL = " select ID, projectName, Description from projectstable"&_ 
      " where (projectName like '%" & search & "%' or description like '%" & search & "%')"

With that said, this is vulnerable to sql injection. Do be careful with such an approach. Consider using parameterized queries instead.

Upvotes: 2

Related Questions