Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible query problem? 1

Status
Not open for further replies.

brad1978

Programmer
Feb 13, 2009
30
CA
Hey all,
So I have an asp/sql issue. I'm building a staff search function and am having a strange issue arise when passing letters via the querystring to a page that searches lastname based on the value in the query string. I am passing the letter, grabing it from the query string and creating my SQL properly, but there are no results. If I copy the outputted query and run it against my db, it works as it should. Any help here would be greatly appreciated.
Code:
<%' Declare all of the variables that will be used in the page.
				Dim objConn			' ADO Connection Object
				Dim objRst			' ADO Recordset Object
				Dim strYear			' The year that we are searching for.
				Dim Sql				' Our SQL statement
				Dim intPageCount		' The number of pages in the recordset.
				Dim intRecordCount		' The number of records in the recordset.
				Dim intPage			' The current page that we are on.
				Dim intRecord			' Counter used to iterate through the recordset.
				Dim intStart			' The record that we are starting on.
				Dim intFinish			' The record that we are finishing on.
				
				dim alpha
				alpha = request.QueryString("alpha")
				response.write(alpha)
				' Check to see if there is value in the NAV querystring.  If there
				' is, we know that the client is using the Next and/or Prev hyperlinks
				' to navigate the recordset.
				If Request.QueryString("NAV") = "" Then
					intPage = 1	
				Else
					intPage = Request.QueryString("NAV")
				End If
				
				SET objConn = server.CreateObject("ADODB.Connection")
				objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
				objConn.Open = "C:\inetpub\[URL unfurl="true"]wwwroot\NEW_ETD\staff\directory.mdb"[/URL]
				
				Sql = "select * from staff where lastname like '" & alpha & "*' order by lastname ASC;"		
				response.write("<br />" & Sql)			
				
				' Create you Recordset Object
				Set objRst = Server.CreateObject("ADODB.Recordset")
				
				' The CursorLocation and the CursorType must be set as they are here
				' in order for Recordset Paging to work properly.
				objRst.CursorLocation = 3	'adUseClient
				objRst.CursorType = 3		'adOpenStatic
				objRst.ActiveConnection = objConn
				
				' Open the recordset.
				objRst.Open Sql
				'objRst.Open Sql, objConn
				
				' Set the PageSize, CacheSize, and populate the intPageCount and 
				' intRecordCount variables.
				objRst.PageSize = 15		
				' The cachesize property sets the number of records that will be cached 
				' locally in memory.
				'objRst.CacheSize = 0 'objRst.PageSize
				intPageCount = objRst.PageCount 
				intRecordCount = objRst.RecordCount 
				
				
				' Now you must double check to make sure that you are not before the start
				' or beyond end of the recordset.  If you are beyond the end, set 
				' the current page equal to the last page of the recordset.  If you are
				' before the start, set the current page equal to the start of the recordset.
				If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
				If CInt(intPage) <= 0 Then intPage = 1
				
				' Make sure that the recordset is not empty.  If it is not, then set the 
				' AbsolutePage property and populate the intStart and the intFinish variables.
				If intRecordCount > 0 Then
					objRst.AbsolutePage = intPage
					intStart = objRst.AbsolutePosition
					If CInt(intPage) = CInt(intPageCount) Then
						intFinish = intRecordCount
					Else
						intFinish = intStart + (objRst.PageSize - 1)
					End if
				End If
				%>
				<%If intRecordCount > 0 Then
					' Display the record that you are starting on and the record
					' that you are finishing on for this page by writing out the
					' values in the intStart and the intFinish variables.
				%>
                	<h2>Staff by Branch Results</h2><br />
                    <p>Results <%=intStart%> - <%=intFinish%> of <%=intRecordCount%> for <em>"<%=alpha%>"</em>.</p>
             		<%
						' Check to see if the current page is greater than the first page
						' in the recordset.  If it is, then add a "Previous" link.
						If cInt(intPage) > 1 Then
					%>
					   <a href="staffbybranchresults.asp?NAV=<%=intPage - 1%>&alpha=<%=alpha%>" class="redlink"><< Prev </a>
					<%End IF%>
					<%
						' Check to see if the current page is less than the last page
						' in the recordset.  If it is, then add a "Next" link.
						If cInt(intPage) < cInt(intPageCount) Then
					%>
					   &nbsp;<a href="staffbybranchresults.asp?NAV=<%=intPage + 1%>&alpha=<%=alpha%>" class="redlink">Next >></a>
                       
                       <a href="export_branch_excel.asp?alpha=<%=alpha%>" target="_blank" class="redlink">Printer Friendly</a>
					<%End If%>
					
					<%
						' Iterate through the recordset until we reach the end of the page
						' or the last record in the recordset.
						dim rowcount
						rowcount = 1					
						response.Write("<table cellpadding=""3"" cellspacing=""0"" border=""0"" style=""border:1px solid black;"">")
							response.Write("<tr>")
								response.Write("<td width=""12%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">First Name</td>")
								response.Write("<td width=""12%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Last Name</td>")
								response.Write("<td width=""20%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Position</td>")
								response.Write("<td width=""15%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Phone Number</td>")	
								response.Write("<td width=""21%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Branch / Region</td>")
								response.write("<td width=""20%"" align=""center"" bgcolor=""#4f0135"" style=""color:white;"">Unit</td>")
							response.Write ("</tr>")
						For intRecord = 1 to objRst.PageSize
							if(rowcount = 1) then						
								response.Write("<tr class=""evenRow"">")
							else
								response.Write("<tr class=""oddRow"">")
							end if
								response.Write("<td>" & objRst("firstname") & "</td>")
								response.Write("<td>" & objRst("lastname") & "</td>")
								response.Write("<td>" & objRst("position") & "</td>")
								response.Write("<td>" & objRst("phonenumber") & "</td>")	
								response.Write("<td>" & objRst("branchregion") & "</td>")
								response.Write("<td>" & objRst("unit") & "</td>")
							response.Write ("</tr>")
							
							if(rowcount = 1) then
								rowcount = 2
							else
								rowcount = 1
							end if
							
							objRst.MoveNext
							If objRst.EOF Then Exit for
						Next
						response.write("</table><br />")
						
						response.Write("<p>Search by last name <br />")
						response.write("<a href=""staffsearch.asp?alpha='a'"" class=""redlink"">A</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha='b'"" class=""redlink"">B</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha='c'"" class=""redlink"">C</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=d"" class=""redlink"">D</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=e"" class=""redlink"">E</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=f"" class=""redlink"">F</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=g"" class=""redlink"">G</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=h"" class=""redlink"">H</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=i"" class=""redlink"">I</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=j"" class=""redlink"">J</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=k"" class=""redlink"">K</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=l"" class=""redlink"">L</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=m"" class=""redlink"">M</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=n"" class=""redlink"">N</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=o"" class=""redlink"">O</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=p"" class=""redlink"">P</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=q"" class=""redlink"">Q</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=r"" class=""redlink"">R</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=s"" class=""redlink"">S</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=t"" class=""redlink"">T</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=u"" class=""redlink"">U</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=v"" class=""redlink"">V</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=w"" class=""redlink"">W</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=x"" class=""redlink"">X</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=y"" class=""redlink"">Y</a>&nbsp;")
						response.write("<a href=""staffsearch.asp?alpha=z"" class=""redlink"">Z</a></p>")
					%>
			<%End If%>
 
try changing:

[blue][tt]
Sql = "select * from staff where lastname like '" & alpha & "*' order by lastname ASC;"
[/tt][/blue]

to

Code:
Sql = "select * from staff where lastname like '" & alpha & "[!]%[/!]' order by lastname ASC;"

When you open Access and run a query, it is probably use DAO as the underlying connection object. Your code is using ADO to connect. ADO does some "stuff" to your queries, but ultimately, it uses a % as the wildcard symbol instead of *.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
gmmastros,
Thanks for the help! I knew it had to be something dumb.
Brad
 
I'm glad that helped.

If this is a public facing website, I strongly encourage you to "beef up" the security.

Code:
alpha = request.QueryString("alpha")

Sql = "select * from staff where lastname like '" & alpha & "%' order by lastname ASC;"

There are a couple problems with this code. To solve the problem, you should really start using command objects.

For example, suppose someone puts [!]A'[/!] in to the search box. The sql would look like:

[tt]select * from staff where lastname like 'A'%' order by lastname ASC;[/tt]

This query will give you an error because there are too many single quotes.

Also... suppose someone puts [!]%' or '%'='[/!] Your query would look like this:

[tt]select * from staff where lastname like '%' or '%'='%' order by lastname ASC;[/tt]

This will return all the data in the table.

Or suppose someone puts this:
[!]%'; Drop Table staff;[/!]

Your query will end up looking like this:

[tt]select * from staff where lastname like '%';Drop Table Staff; order by lastname ASC;[/tt]

The last part (the order by) will error out because it's not valid SQL, but you will lose your staff table. This bit of nastiness is call SQL Injection, and it's very real.

If you use command objects, this cannot happen. Seriously, spend a couple minutes learning about this (and how to accommodate it). In the long run, you'll be glad you did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top