Connecting to Oracle using Forms

Inputting a value

Getting a value from the user and submitting it to a database requires the use of a form.

You therefore need two pages, a "front" page with the form to get the user input and a "back" page with the ASP code, to connect to the database.

This example also makes use of a function query2table. This formats the results from the database into a html table to make the results look better. Since it is a useful function that could be used in further ASP pages, it has been saved to a library file: ASPLib.asp

Warning!

These examples were put together years ago, long before SQL-Injection was heard of, or understood. Allowing the user to type the value in the fields is not recommended, since it leaves it open to SQL-Injection and other nasties!

This is similar to the previous Emp form example. The difference is only one field is returned to make it more vunerable to UNION attacks.

It is better practice to allow the user to pick a value from a list instead. See this example instead, which uses Access, but the concepts are equally applicable to Oracle.

html Form

<html>
<head>
<title>Forms and a Database</title>
</head>

<body>
<h2>Connecting to Oracle using a Form</h2>
<form action="employeeList.asp" method="post">
<p>An example form to enter search for employees from a particular department.</p>
<P>Enter department number (10, 20, 30, 40, 90)
<INPUT TYPE="text" NAME="deptno"> </p>
<p><input type="submit" value="Search Database"> </p>
</form>
</body>
</html>

ASP Code

<html>
<head>
<title>Employee List</title>
</head>
<body>

<h2><font color= "blue">Employee List</font></h2>

<!--#include file="_lib\ASPLib.asp"-->

<% SET myConn=SERVER.CreateObject("adodb.connection")
myConn.Open "Provider=MSDAORA; Data Source=ora12c;" & _
      "User ID=MyOracleUsername;" & "Password=MyOraclePassword"

' request gets the value of the deptno variable
' This is what you should not do and makes the website vunerable to SQL Injection attacks:
SQLStr="SELECT ename from Emp WHERE deptno=" & request("deptno")

' see the string generated
'(Note, you should only show this when testing a website)
response.write ("'" & SQLStr &"'")
CALL query2table(SQLStr,myConn)
myConn.Close %>

</body>
</html

ASP Include File

<% SUB query2table(inputquery, conntemp)
DIM rstemp
SET rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>

<table border=2><tr>
<% 'Put Headings On The table of Field Names
for i=0 to howmanyfields %>
<td><B><%=rstemp(i).name%></B></td>
<% NEXT %>
</tr>
<% ' Now lets print the records
DO WHILE NOT rstemp.EOF %>
<tr>
<% FOR i=0 TO howmanyfields
thisvalue=rstemp(i)

' hardcode a space if value blank
IF ISNULL(thisvalue) THEN
thisvalue=" "
END IF%> <td VALINE=TOP><%=thisvalue %></td>
<% NEXT %>
</tr>

<%rstemp.MoveNext
LOOP %>
</table>

<% ' close the connections
rstemp.Close
SET rstemp=nothing
END SUB %>