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.
The next step is to add our ASP code in between the starting and ending
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 oCmd
Well, 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 & “
”)
next
Response.Write("
")
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.
empID=3 empName=Jim Bore empTitle=Accountant
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 = " & qs
NOTE: 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) = qs
By using Parameters, we can safely send the database the SQL command including the query string data as a literal.
