Quering a DB with a Hyperlink

[Markus]

Hallo I hope some one can help me i have a hyperlink that i want to use to
query a record in a DB. Can some one tell me what im doing wrong it does not
query the Request_ID 1

This is the hyperlink example : http://spi.com/acr/audit.asp?Request_ID=1

<%
Dim rs, strPfadDB, SQL, CDSID1
strPfadDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("\fpdb\request_table.mdb") & _
";Mode=ReadWrite;Persist Security Info=False"
SQL ="SELECT * " & _
"FROM Audit " & _
"WHERE (Request_ID = '::Request_ID::')"

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, strPfadDB, 1, 2
CDSID1 = (rs("CDS_ID"))
rs.Close
set rs = nothing
%>

 

[Ronx]

Assumes Request_ID should always be an integer

<%
Dim rs, strPfadDB, SQL, CDSID1, stmp, itmp
stmp = request("Request_ID") & ""
stmp = trim(stmp)
if isNumeric(stmp) then
itmp = cInt(stmp)
strPfadDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("\fpdb\request_table.mdb") & _
";Mode=ReadWrite;Persist Security Info=False"
SQL ="SELECT * " & _
"FROM Audit " & _
"WHERE (Request_ID = " & itmp & ")"

'if reference_ID is numeric, don't add quotes round it in WHERE
statement
'::xxxx:: is a FrontPage DBR reference, not asp

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, strPfadDB, 1, 2
CDSID1 = (rs("CDS_ID"))
rs.Close
set rs = nothing
%>

 

[Stefan B Rusynko]

Since the OP is apparently not familiar w/ hand coding, the code sample should be complete
- the IF was missing the required End If

<%
Dim rs, strPfadDB, SQL, CDSID1, stmp, itmp
stmp = request("Request_ID") & ""
stmp = trim(stmp)
'code to test parameter value and prevent SQL injection here
IF isNumeric(stmp) then
itmp = cInt(stmp)
strPfadDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("\fpdb\request_table.mdb") & _
";Mode=ReadWrite;Persist Security Info=False"
SQL ="SELECT * " & _
"FROM Audit " & _
"WHERE (Request_ID = " & itmp & ")"
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, strPfadDB, 1, 2
IF NOT rs.EOF THEN
CDSID1 = (rs("CDS_ID"))
ELSE
' code if record not found
END IF
rs.Close
set rs = nothing
ELSE
'code if parameter is invalid
END IF
%>

While that will work
- use of any query string for a record ID as a parameter link always leaves the Site/DB open for a SQL injection attack by a hacker
(if a query string is used, it should always include robust server side preventative code to prevent SQL injection attack -
isNumeric alone is not adequate as a check if the hacker uses hex)

PS

Also no need to use the DB field name in the parameters as
http://spi.com/acr/audit.asp?Request_ID=1
A shorter generic parameter can also be used as say:
http://spi.com/acr/audit.asp?I=1
and parsed as:
stmp = request("I") & ""


--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


Assumes Request_ID should always be an integer

<%
Dim rs, strPfadDB, SQL, CDSID1, stmp, itmp
stmp = request("Request_ID") & ""
stmp = trim(stmp)
if isNumeric(stmp) then
itmp = cInt(stmp)
strPfadDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("\fpdb\request_table.mdb") & _
";Mode=ReadWrite;Persist Security Info=False"
SQL ="SELECT * " & _
"FROM Audit " & _
"WHERE (Request_ID = " & itmp & ")"

'if reference_ID is numeric, don't add quotes round it in WHERE
statement
'::xxxx:: is a FrontPage DBR reference, not asp

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, strPfadDB, 1, 2
CDSID1 = (rs("CDS_ID"))
rs.Close
set rs = nothing
%>