query on sql query and asp code [Archive]

View Full Version : query on sql query and asp code

yaggy85

02-06-2009, 06:53 AM

Hi guys,

im trying to select all the records in my database, which have the "processed" field as "no".

my script below works with the following SQL line:
SQL = "Select * FROM " & tbl

but when i change it to this:
SQL = "Select * FROM " & tbl & " WHERE processed = " & pro

I change it to that so that instead of selecting all entries, it only selects the records where "processed" = "no"

I get the following error:

Select * FROM products WHERE processed = no
Sorry, no records were found. Back To Admin Mainproducts

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/com_1/delete32.asp, line 80

I don't understand what i'm doing wrong? i always response.write my sql so i know the SQL is ok. I'm thinking the error is coming from something in the main code where it sorts the entries into 200 per page..

Anyone able to help?

Cheers.

<%Response.Buffer=TRUE%>
<%IF session("admin") = FALSE THEN Response.Redirect "admin.asp"%>
<%
tbl = Request.QueryString("which")
If tbl = "products" Then
ttl = "products"

pro = Request.QueryString("processed")

Else
ttl = "Administrators"
End If
%>
<html>
<head>
<title>Delete <%=ttl%></title>
</head>
<body>
<%
'here is the connection string
Set conn = server.createobject("adodb.connection")
'this connection uses JET 4 it is the prefered method of connecting to an access database
DSNtemp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/com_1/database/ra_db.mdb")
'if you cant use JET then comment out the line above and uncomment the line below
'DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("/com_1/database/ra_db.mdb")
conn.Open DSNtemp

SQL = "Select * FROM " & tbl & " WHERE processed = " & pro
response.write(sql)

pagetot = 200

const adOpenForwardOnly = 0
const adOpenKeySet = 1
const adOpenDynamic = 2
const adOpenStatic = 3
const adLockReadOnly = 1
const adLockPessimistic = 2
const adLockOptimistic = 3
const adLockBatchOptimistic = 4
Set RS = Server.CreateObject("Adodb.RecordSet")
RS.Open SQL, Conn, adopenkeyset, adlockoptimistic
If RS.EOF Then
%>
<table cellspacing=1 border=0 cellpadding=2 width="100%">
<tr>
<td> Sorry, no records were
found. <a href="admin2.asp">Back To Admin
Main</a><%=ttl%> </td>
</tr>
</table>
<%
ElseIf request("begin") = "" Then
begin = 1
Else
begin = Cint(request("begin"))
End If

tot = ((begin+pagetot)-1)

If tot > RS.RecordCount Then
tot = RS.RecordCount
End If

i = 1
navi = ""
For j = 1 to RS.RecordCount step pagetot
If j <> 1 Then navi = navi & " | "
If j = Cint(begin) Then
navi = navi & ""&i&""
Else
navi = navi & "<a href="""
navi = navi & Request.ServerVariables("url") & "?begin=" & j & "&which=" & tbl
navi = navi & """>"
navi = navi & i & "</a>"
End If
i = i + 1
Next
i = 1
RS.Move begin - 1
%>
<h1>View RA Requests - Processing Area</h1>
<a href="admin2.asp">Back To Admin Main</a>
<table>
<table cellspacing=0 border=1 cellpadding=6 width="100%" bordercolorlight="#000000" bordercolordark="#000000">
<tr>
<th align=Left>Store Name</th>
<th align=Left>Store Phone</th>
<th align=Left>Store Email</th>
<th align=Left>Contact</th>
<th align=Left>Request Type</th>
<th align=Left>RA #</th>
<th align=center>Invoice</th>
<th align=center>Product Code</th>
<th align=center>Fault</th>
<th align=center>Time Requested</th>
<th align=center>Processed</th>
<th align=center>Action</th>
</tr>
<%
For x = begin to begin + (pagetot - 1)
If RS.EOF Then exit For
%>
<tr>
<td align=Left><%=RS("storename")%></td>
<td align=Left><%=RS("storephone")%></td>
<td align=Left><%=RS("storeemail")%></td>
<td align=Left><%=RS("contactname")%></td>
<td align=Left><%=RS("requesttype")%></td>
<td align=Left><%=RS("id")%></td>
<td align=Left><%=RS("invoice")%></td>
<td align=Left><%=RS("productcode")%></td>
<td align=Left><%=RS("fault")%></td>
<td align=Left><%=RS("time_requested")%></td>
<td align=Left><%=RS("processed")%></td>
<td align=center><a href="edit_ra_script2.asp?which=<%=tbl%>&id=<%=RS("id")%>">Process</a></td>
<td align=center><a href="edit_ra_entry.asp?which=<%=tbl%>&id=<%=RS("id")%>">Edit</a></td>
</tr>
<%
i = i + 1
RS.MoveNext
Next
%>
</table>
<table cellspacing="2" cellpadding="2" width=100%>
<tr>
<td align=right>



<%=ttl%> <%=begin%> - <%=tot%> of <%=RS.RecordCount%> Page: <%=navi%>

</td>
</tr>
</table>
<%
RS.Close
Set RS = nothing
Conn.close
Set Conn = Nothing
%>
</body>

Spudhead

02-06-2009, 11:11 AM

Two things.

One:

pro = Request.QueryString("processed")

This lets people put anything they want into your SQL. What if I went to:

yourpage.asp?pro=;DROP TABLE Users

?

Google for "SQL Injection" and always clean your user input before using it.

Two - on line 43 of what you posted, you check for RS.EOF - this is good. But that IF statement ends on line 57, and you've got a load of stuff after that (like line 80, the one that's causing the error by calling RS.Move) that needs a recordset with records. I'd wrap pretty much all of the code below line 57 in that IF statement.

TheShaner

02-06-2009, 07:29 PM

I second Spudhead's assessments. You definitely need to check the data you receive. If you know the values that the data should be, check it before using it in a query, i.e. if pro can only be "yes" or "no", do something like:
SQL = "Select * FROM " & tbl & " WHERE processed = """ & cleanPro(pro) & """"
...
Function cleanPro(pro)
If (pro == "yes") Then
cleanPro = "yes"
Else
cleanPro = "no"
End Function
This will then assure that the input will always be either "yes" or "no".
If you don't know what the data is going to be, you'll have to create a function that escapes any bad characters:
SQL = "Select * FROM " & tbl & " WHERE processed = """ & cleanInput(pro) & """"
...
Function cleanInput(userInput)
cleanInput = Replace(userInput, "'", "''")
End Function
The above is an example and will not prevent all SQL injections. Read this article: http://www.4guysfromrolla.com/webtech/061902-1.shtml

Also, what I did in both examples regarding your SQL statement is I placed quotation marks before and after the pro variable since "yes" and "no" should be treated as a string. To add quotation marks, you must use two of them. This is called escaping. Since a quotation is a special character that marks the beginning and end of a string, you must use two in a row to actually use the quotation as a character within the string.

And as Spudhead said, your If-Then statements are a little screwed up. You need to clean it up so that the code that relies on RS being populated does not execute if RS.EOF is true.

-Shane

demtron

02-06-2009, 08:00 PM

In response to the whole SQL injection discussion, I would also consider creating the DB connection with an account that has limited access - such as only belonging to the db_datareader role. For those activities that require write access, scrubbing the criteria will still be required. Another idea would be to truncate the provided parameter for everything after the semicolon.

yaggy85

02-09-2009, 02:09 AM

hi guys,

sql injection isnt a problem, because i will be the only one using this side of the script. my admin login pages all have sql injection protection..

anyway, i have cleaned up the code below:

<%Response.Buffer=TRUE%>
<%IF session("admin") = FALSE THEN Response.Redirect "admin.asp"%>
<%
tbl = Request.QueryString("which")
If tbl = "products" Then
ttl = "products"
Else
ttl = "Administrators"
End If

pro = Request.QueryString("processed")

%>
<html>
<head>
<title>Delete <%=ttl%></title>
</head>
<body>
<%

Set conn = server.createobject("adodb.connection")

DSNtemp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/com_1/database/ra_db.mdb")

conn.Open DSNtemp

SQL = "Select * FROM " & tbl & " WHERE processed = " & pro

response.write(sql)

Set RS = Server.CreateObject("Adodb.RecordSet")
RS.Open SQL, Conn
%>

<h1>View RA Requests - Processing Area</h1>
<a href="admin2.asp">Back To Admin Main</a>
<table>
<table cellspacing=0 border=1 cellpadding=6 width="100%" bordercolorlight="#000000" bordercolordark="#000000">
<tr>
<th align=Left>Store Name</th>
<th align=Left>Store Phone</th>
<th align=Left>Store Email</th>
<th align=Left>Contact</th>
<th align=Left>Request Type</th>
<th align=Left>RA #</th>
<th align=center>Invoice</th>
<th align=center>Product Code</th>
<th align=center>Fault</th>
<th align=center>Time Requested</th>
<th align=center>Processed</th>
<th align=center>Action</th>
</tr>

<tr>
<td align=Left><%=RS("storename")%></td>
<td align=Left><%=RS("storephone")%></td>
<td align=Left><%=RS("storeemail")%></td>
<td align=Left><%=RS("contactname")%></td>
<td align=Left><%=RS("requesttype")%></td>
<td align=Left><%=RS("id")%></td>
<td align=Left><%=RS("invoice")%></td>
<td align=Left><%=RS("productcode")%></td>
<td align=Left><%=RS("fault")%></td>
<td align=Left><%=RS("time_requested")%></td>
<td align=Left><%=RS("processed")%></td>
<td align=center><a href="edit_ra_script2.asp?which=<%=tbl%>&id=<%=RS("id")%>">Process</a></td>
<td align=center><a href="edit_ra_entry.asp?which=<%=tbl%>&id=<%=RS("id")%>">Edit</a></td>
</tr>

</table>
<table cellspacing="2" cellpadding="2" width=100%>
<tr>
<td align=right>




</td>
</tr>
</table>
<%
RS.Close
Set RS = nothing
Conn.close
Set Conn = Nothing
%>
</body>

I still get the following error:

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/com_1/delete32.asp, line 0

anyone know why this might be happening?

yaggy85

02-09-2009, 02:44 AM

ok, it seems as though when i set processed to yes, it will display the first record in my DB only, and nothing else.

when i set it back to no, i get the above error.

what is happening?

shakir

02-09-2009, 08:32 AM

Check the query below

SQL = "Select * FROM " & tbl & " WHERE processed = '" & pro & "'"

The query should be fired like below

Select * FROM tablename WHERE processed = 'no'

Old Pedant

02-11-2009, 01:14 AM

This lets people put anything they want into your SQL. What if I went to:

yourpage.asp?pro=;DROP TABLE Users

Well, it WOULD allow that...if this wasn't Access/JET. Access/JET doesn't allow more than one SQL statement per query. Still, it could allow unexpected results from weird input. (Irrelevant in this case, since he said it's admin only and protected, of course.)