Database Access using AOLServer and the ACS

Brief Introduction to Database Access using AOLServer and the ACS

written by Ravi Jasuja and Neal Sidhwaney with portions taken from the Database Access API doc written by Jon Salz

Database access from AOLServer TCL scripts is done through an API provided by the Arsdigita Community System.

Bind Variables

Before we go into details about the API, it is worth taking a moment to step back and consider how we will present data to Oracle. The obvious answer is to simply have TCL variable references within our SQL/DML statements that are interpolated by the TCL interpreter. Consider the following query:

select name from users where user_id = $current_user

If the variable is interpolated before the query is presented to Oracle, the query could actually end up being different from what is written in our code! If the variable current_user had the value "1 or 1=1", the above query turns into:

select name from users where user_id = 1 or 1=1

and a query that normally returns 1 row is now returning all the rows in the users table. Since a great deal of web programming is running database commands depending on user input, this is a huge security problem.

The solution

To get around this, we use bind variables. Essentially, we put placeholders in our database commands and give Oracle the values for these placeholders. By leaving it up to Oracle to perform the variable interpolation, we eliminate the possibility of clauses being unknowingly inserted into our queries or DML statements(because Oracle will parse the query before interpolating the variables).

The syntax

The API provides several ways to associate bind variables with a query, but the simplest is to simply put :varname in your SQL or DML statements wherever you have a TCL variable(with exceptions; see below). The above query would then become:

select name from users where user_id = :user_id

The API will pull the value of user_id out of the environment and take care of presenting it to Oracle in the correct manner. Inserts and updates work similarly:

insert into foo (foo_id, foo_name) values (:thisfoo_id,:thisfoo_name)
update foo set foo_name = :newfooname where foo_id = :thisfoo_id

You cannot, however, use bind variables for table names or the columns used in clauses,i.e.:

select from :anytable where somecolumn = 4
select from foo where :anycolumn = 'bar'

A general rule of thumb is that bind variables can only be used with table data or comparisons with table data.

The API

These are the basic commands for running DB queries and DML statements that will get you through pset 1; a more comprehensive overview can be found here.

db_null

This provides a database-independent way to represent null (instead of the Oracle-specific idiom of the empty string). Use it instead of the empty string whenever you want to set a column value explicitly to null, e.g.:

set bar [db_null]
set baz [db_null]

db_dml foo_create "insert into foo(bar, baz) values(:bar, :baz)"
#
# sets the values for both the "bar" and "baz" columns to null

db_nextval sequence

Returns the next value for a sequence.

db_foreach statement-name sql 
    code_block [ if_no_rows if_no_rows_block ]

Performs the SQL query sql, executing code_block once for each row with variables set to column values (or a set or array populated if -column_array or column_set is specified). If the query returns no rows, executes if_no_rows_block (if provided).

Example:

db_foreach select_foo "select foo, bar from greeble" {
    doc_body_append "<li>foo=$foo; bar=$bar\n"
} if_no_rows {
    doc_body_append "<li>There are no greebles in the database.\n"
}

The code block may contain break statements (which terminate the loop and flush the database handle) and continue statements (which continue to the next row of the loop).

db_1row statement-name sql

Performs the SQL query sql, setting variables to column values. Raises an error if the query does not return exactly 1 row.

Example:

db_1row select_foo "select foo, bar from greeble where greeble_id = $greeble_id"
# Bombs if there's no such greeble!
# Now $foo and $bar are set.

db_0or1row statement-name sql

Performs the SQL query sql. If a row is returned, sets variables to column values and returns 1. If no rows are returned, returns 0. If more than one row is returned, throws an error.

db_string statement-name sql

Returns the first column of the result of SQL query sql. If sql doesn't return a row, returns default (or throws an error if default is unspecified).

db_list statement-name sql

Returns a Tcl list of the values in the first column of the result of SQL query sql. If sql doesn't return any rows, returns an empty list.

db_list_of_lists statement-name sql

Returns a Tcl list, each element of which is a list of all column values in a row of the result of SQL query sql. If sql doesn't return any rows, returns an empty list.

db_dml statement-name sql

Performs the DML or DDL statement sql.

Example:

db_dml change_foo_name "update bar set foo_name = 'Joe Smith' where foo_id = :thisfooid

db_release_unused_handles

Releases currently unused database handles. This is normally done when the execution of the script is terminated, but you can provide a hint to release unused handles earlier if you have a lot code that doesn't access the database. The handle will then be available for other scripts.

neal@arsdigita.com

Last modified: August 25 2000, 5:56 PM Add a comment | Add a link