In most cases, if you are running a web application that includes server-side scripting such as ASP, you are most likely providing dynamic content to your visitors by accepting input and displaying the results from data stored in a database such as MySQL or MSSQL.
In this tutorial, we will cover how to create a few SQL queries that you can use to retrieve information from your database.
In the following examples, we will connect to a MySQL database and query records from a table called Employees. The first step is to create an ASP page and include some of the typical HTML components.
<!DOCTYPE html>
<html>
<head>
<title>My Page</title>
</head>
<body>
<-- Add in HTML and ASP Code -->
</body>
</html>The next step is to add our ASP code in between the starting and ending <body> tags. Our beginning ASP code will simply include creating our variables and objects. Notice where we assign our SQL query to the sql variable.
<%
Dim oConn 'Connection Object
Dim oRS 'Recordset Object
Dim ds 'datasource
Dim qs 'query string
Dim sql 'sql statement
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
qs = Request.Querystring("id")
sql = "SELECT * FROM employees"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open ds
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Prepared = True
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open oCmdWell, get back to the ‘sql’ variable in a moment because we will be looking at different examples by modifying this statement. The next step is to display the results. We use a loop to go through the Fields in the Recordset. Finally, tidy up.
do until oRS.EOF
for each x in oRS.Fields
Response.Write(x.name & "=" & x.value & "<br />")
next
Response.Write("<br />")
oRS.MoveNext
loop
oRS.close
oConn.close
Set oRS=nothing
Set oConn=nothing
%>The sample results are as follows:
empID=1
empName=John Smith
empTitle=Sales Associate
empID=2
empName=Jane White
empTitle=CEO
empID=3
empName=Jim Bore
empTitle=Accountant
Query for a Specific Record
If you were interested in only querying a subset of the data, we can modify our SQL query by using the WHERE clause. In the following example, we only want to query the database for an employee that has an ID of 2. We can simply modify the variable sql as follows.
sql = "SELECT * FROM employees WHERE empID = 2"However, to make the website more dynamic, we would prefer to have the information provided by the user. In this case, we will assume the scenario where a user clicked a link on another web page in the application and the link contained the information in the query string. We can modify the syntax as follows.
sql = "SELECT * FROM employees WHERE empID = " & qsNOTE: The previous example should not be implemented. While it will technically work, it is vulnerable to SQL Injection because the query string can be modified to include additional SQL commands. The recommended approach is to use parameters. Here is an example. First, change the SQL query to include a parameter by using a ‘?’ and secondly add the parameter to the Command object.
sql = "SELECT * FROM employees WHERE empID = ?" In the following section, add the parameter.. oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Parameters(0) = qsBy using Parameters, we can safely send the database the SQL command including the query string data as a literal.
