This is a manual for the CGI program WWW-SQL, written by James Henstridge. An online copy of this document exists here. It is part of the WWW-SQL web site. If you have some spare time, go to the site to fill out the WWW-SQL survey (Powered by www-sql, of course).
WWW-SQL is a CGI program designed to create web pages from information in MySQL or PostgreSQL databases on the fly. It was written by James Henstridge (james@daa.com.au). Its syntax is similar to that of W3-mSQL which is a product of Hughes Software, and was designed for mSQL. This program is distributed under the terms of the GPL, and doesn't share any code with W3-mSQL (although I did use its syntax as a model).
WWW-SQL is a CGI program that passes another HTML page, extracting special tags, parsing them and inserting the results in the resulting document. As you may have guessed, the commands this preprocessor uses are related to lookups of SQL databases. The command set contains conditional execution, execution of queries and expansion of CGI variables and query fields.
It should be noted that www-sql is modelled around the interface of the W3-mSQL distributed for mSQL 1.x. It is not compatible with, or similar to the command set of the W3-mSQL (a.k.a Lite) distributed with mSQL 2.x. If you require a tool of similar complexity, maybe you should look at PHP/FI. It is a more general purpose programming language that is embeded in HTML documants.
Since the first versions, there have been a number of aditional features added. One such feature is a recursive version of www-sql. After it parses the script it re-parses the script, until there are no www-sql tags left. This means that embedded commands will be executed after all the non embedded commands. This particular feature was submitted by Simon Cocking <simon@ibs.com.au>.
I have only tested this program on Linux, but it should compile without much of modification on most other modern unix clones. If you need to modify the source to get it to compile, please send a diff to me at james@daa.com.au so that I can integrate it into the next release. To help compile it on other systems, I have adapted this package to use autoconf, so first try running ./configure, or if you want the recursive version try running ./configure --enable-recursive.
If that doesn't work, there are a number of possible causes.
/usr/local/mysql/lib or
/usr/lib/mysql or /usr/local/lib/mysql, and
it searches for mysql.h in the include search path, or
in /usr/include/mysql or /usr/local/mysql/include.
If the library and header are located at a different place, you must pass a
couple of arguments to ./configure. You can add the argument
--with-mysql-headers=dir to specify the location
of the mysql.h header. The other
argument is --with-mysql-libs=dir where the
mysqlclient library is located. (Note that you can still use the old
MYSQL_INCDIR and MYSQL_LIBDIR variables to do this).
--with-pgsql-headers=dir and
--with-pgsql-libs=dir. The default search path
for PostgreSQL related stuff is also different (and relevant to PostgreSQL).
Now you are ready to run make. Usually, no parameters are needed, but if you want to use different default SQL user and password, run make as follows:
make SQL_USER=user SQL_PASS=pass
The current values should work on Redhat Linux systems that used the RPMs found at the MySQL distribution site. Now run make with no parameters. If the program compiles successfully, try giving it a small test:
cat << EOF > test.in <html> <head><title>test</title></head> <body> Answer to life, the universe and everything = <!sql print $answer> </body></html> EOF env REQUEST_METHOD=GET QUERY_STRING='answer=forty+two' \ PATH_TRANSLATED=test.in REMOTE_ADDR=127.0.0.1 \ REMOTE_HOST=localhost www-sql
This should write a small HTML document saying "Answer to life, the universe and everything = forty two" to the standard output.
Now we are ready to install the binary. If your CGI-BIN
directory is not one of /home/httpd/cgi-bin,
/var/lib/httpd/cgi-bin or
/usr/local/etc/httpd/cgi-bin, you must specify the directory on
the command line. Simply type
make install CGI_DIR=cgi-dirOtherwise just type make install. If your web server is installed in a different, standard place, please notify me so I can include it in the next release. The values I have given are just those I have encountered with Redhat Linux, Slackware Linux, and the standard install directory for NCSA and Apache web servers.
Now that the binary compiled and installed, you can write WWW-SQL
documents, and place them in your document tree. Now to parse
a document that would usually be accessed by the URL
http://host/doc.html,
use the URL http://host/cgi-bin/www-sql/doc.html.
One thing you must remember when calling up this document, is
that the web browser will think that the current directory is
/cgi-bin/www-sql, and all links will be relative
to this.
If you use Apache web server, there is an elegant way round this.
After installing the binary, edit your srm.conf file,
and insert the following lines:
Action www-sql /cgi-bin/www-sql AddHandler www-sql sqlIf you do add these lines, to your configuration files, and always use www-sql as an action handler, you should consider passing configure the argument
--enable-apache-action-check to seal off a
security hole, when you compile www-sql.
Now documents with the extension .sql will be parsed
by WWW-SQL, without having to actually reference WWW-SQL in the
URL. This also prevents people from accessing the unparsed document
and stealing passwords from the connect command.
So you could rename doc.html to doc.sql,
and use the URL http://host/doc.sql.
Now you need to set up the default user account for WWW-SQL to use. This can be achieved by typing the following commands for the MySQL version:
mysql -uroot -ppass mysql << EOF
insert into user (Host, User, Password, Select_priv)
values ("localhost", "nobody", "", "Y");
EOF
mysqladmin -uroot -ppass reload
Where pass is the root password. This will give www-sql access to all your databases. In order to be more restrictive, use the following:
mysql -uroot -ppass mysql << EOF
insert into user (Host, User, Password)
values ("localhost", "nobody", "");
EOF
and then, for each database you want to give www-sql access to, type:
mysql -uroot -ppass mysql << EOF
insert into db (Host, Db, User, Select_priv)
values ("localhost&guot;, "db", "nobody", "Y");
EOF
and finally:
mysqladmin -uroot -ppass reload
For PostgreSQL, start by type:
createuser nobodyNow grant permissions to nobody with commands like this:
psql dbname << EOF grant SELECT -- or INSERT, UPDATE, DELETE, RULE, or ALL on tablename to nobody; EOF
The tags recognised by WWW-SQL must be of the following form:
<! SQL command arg1 arg2 ...
>
The meanings of the parameters after the <! SQL
are as follows
The word SQL and the command name are case independent, but the
arguments are not. Also, it doesn't matter if there is space between
the <! and the SQL.
In commands that require the evaluation of expressions (eg. if, elsif, eval, setexpr), the following operators can be used on numbers (floating point or integer): + - * / % ^ ! == != > < >= <= && ||. You can also use & as a synonym for &&, | as a synonym for || and = as a synonym for ==. You can also use brackets to get round normal precedence. The following operators may be used on strings: == != > < >= <= :. All the string operators return a number. The colon operator performs a regex pattern match (eg. "jebediah" : "b.*h").
Before expressions are evaluated, they are checked for variable
expansions (eg. $varname). Also, it is NOT necessary to
put spaces between every token.
In some commands, cgi variables and query results are expanded.
The cgi variables are specified by their name, preceded by a dollar
sign (e.g. $name). The query results for the current
row are specified by the query name preceded by an @, followed
by a dot and the field number or name (e.g. if a query q1 selects the field
surname as the first field, its value on the current row can be accessed
as @q1.0 or @q1.surname).
You can also URL encode a variable of query result value by replacing the @ or $ with a # (e.g. if @q1.0 = 'a +' then #q1.0 = 'a+%2B').
There are also two other ways of encoding, or escaping, a variable. The first is using MySQL's rules (SQL's rules?). This is useful when you are taking input from the user that you are inserting into an SQL statement as a string. This is done by prepending the variable name with a ? instead of a $ or a @. The other method is HTML encoding, which basically converts <, >, & and " to their equivalent entities in HTML. This is useful when you are putting information from a table into the HTML document.
Variable expansion occurs in expressions
as well. When variable expansion occurs, the escape sequences
'\n', '\t', '\$', '\@',
'\#', '\?', '\~ and '\\'
are also expanded.
In the definitions of the commands, required text will be in this
font, variables will be in this font, and optional
parameters will be enclosed in square brackets.
When WWW-SQL parses a document, it sets some extra variables at times. This is a list of those in use so far:
$AFFECTED_ROWS
$NUM_FIELDS
$NUM_ROWS
$WWW_SQL_VERSION
$GATEWAY_INTERFACE
$HOSTTYPE
$HTTP_HOST
$HTTP_REFERER
$HTTP_USER_AGENT
$OSTYPE
$PATH_INFO
$PATH_TRANSLATED
$REMOTE_ADDR
$REMOTE_HOST
$REMOTE_USER
$SERVER_ADMIN
$SERVER_NAME
$SERVER_PORT
$SERVER_PROTOCOL
$SERVER_SOFTWARE
These variables can be accessed in the same way CGI variables may be.
Normally you set cookies by sending the "Set-Cookie" header, but for www-sql, you should put a <META> tag such as this in the HEAD section of the document:
<META http-equiv="Set-Cookie" content="name=value;expires=Friday, 31-Dec-99 23:59:59 GMT; path=/">Note that you can embed www-sql commands into the <META> tag, which you will probably use to set the value of the cookie, and the ftime command to set the expire time. Also note that you are not required to use the expires clause, if you don't want your cookie to last longer than one browser session. For more information on cookies, see Netscape's website.
The cookie reading code was submitted by Lars Bensmann <lars@skynet.e.ruhr.de>
<! SQL break >
This command breaks out of a print_loop or while loop.
It is not available with the old scanner code.
<! SQL close >
This command closes the connection to the database backend. It should be called as the last WWW-SQL command.
<! SQL connect [ host [ user password
] ] >
This command connects you to the MySQL daemon. It should be used before any other SQL commands are used. For the PostgreSQL version, the connection is not made until the database command is run.
mysqld is running.
This defaults to the local host.
mysqld. If this argument
is given, so must password.
Note that the SQL commands are not sent to the client, so the only people who will be able to look at passwords embedded in the documents are people with access to the local host.
<! SQL continue >
This command skips the rest of a print_loop or while loop and continues.
It is not available with the old scanner code.
<! SQL convert varname >
This command converts the value of a variable to a form in which it can be
included in a string for an SQL statement. This function is only provided for
compatibility with W3-mSQL v1.x. It is much easier to use the
?varname syntax.
<! SQL database dbname >
This command says which database to use. The database is specified by dbname. For the PostgreSQL version, this command makes the connection, using the information from the connect command.
<! SQL dumpvars >
This command prints out the names and values of all www-sql variables set at the time of the call.
<! SQL eval expr >
This command evaluates expr, and inserts the result in the document.
<! SQL exec program [args] >
This command executes an external program, and inserts the output into
the document. It is only available if configure was run with the
--enable-unsafe option when compiling www-sql.
This function was submitted by Malte John <malte@ddd.de>.
<! SQL fetch qhandle
>
This command gets the next row from the query corresponding to qhandle.
<! SQL free qhandle >
This command frees the query handle qhandle.
<! SQL ftime format [offset] >
This function prints the time according to the format string given. If the
second argument is given, it acts as an offset to the current time. This
is useful for setting the expire time on cookies. This command is only
enabled if the configure script could find strftime in your
C library. The time offset is given in seconds.
<! SQL if expr
>
...
[ <! SQL elsif expr> ]
[ <! SQL else > ]
...
<! SQL endif >
These commands implement conditional execution. When the if
command is encountered, expr is evaluated. If expr
is evaluated to true, non zero, or a non empty, everything up to an
else, elsif or endif is parsed.
When an else is encountered, if previous commands were
being ignored, the next commands are parsed, and vice versa. When
an elsif is encountered, the parameters are evaluated
as an expression, and if true, the next block of code is parsed. When
an endif is encountered, parsing returns to normal.
The else command can be left out, and if
statements can be nested to any level.
The elsif code was submitted by David J. N. Begley <d.begley@uws.edu.au>.
<! SQL include filename >
Include a file inline into the document. Any tags in that file will be
parsed by www-sql, and that parsing occurs inline. It is also possible
to recursively include documents, up to a limit of 10 documents. (If
this limit is too low for your purposes, change the define MAX_INCLUDE_DEPTH
in scanner.c or scanner.l). Normally, you can only include files that are
in the current directory, but if you gave configure the
--enable-unsafe argument when you built www-sql, you can
include files in any directory. This flag also turns on the exec command.
I received two implementations of this function. One from Alexy I Onin <alexy@vtau-bsd.pstu.ac.ru> and one from Malte John <malte@ddd.de>. The one that finally went into www-sql was from Malte, but thanks to both of you.
<! SQL print text
>
This command expands variables in the argument text and includes the output in the document.
<! SQL print_loop qhandle>
...
<! SQL done>
This command is similar to print_rows, except that instead of expanding a string for each row of the table, print_loop allows you to put arbitrary HTML and www-sql commands inbetween the print_loop and done statements. Like while, you can put break and continue statements inside a print_loop.
This function is not available with the old scanner code.
<! SQL print_rows qhandle
format >
This command is used to quickly print the results from a query
associated with qhandle. It starts from the current
row, evaluates format, outputs it, and goes on to the
next row, and repeats until it reaches the end of the table. In
order to use the results again, the seek command
must be used.
<! SQL query query qhandle
>
This command executes the query query, and associates
the result with qhandle. After the information has
finished being used, the handle must be freed with the free
command.
<! SQL qtable qhandle [borders]
>
Generates a 'quick and easy' HTML table containing all of the rows
from the query represented by qhandle, headed by the field
names. The optional borders keyword switches on borders for
the table. This command is provided as an easy alternative to
print_rows.
This command was originally contributed by Martin Maisey <M.J.Maisey@webname.com>.
<! SQL qlongform qhandle >
Generates a 'quick and easy' long form results page that can be used for display on browsers which don't support tables, and for queries with a lot of resulting rows. The output looks like:
Forename: John
Surname: SmithForename: Joe
Surname: Bloggs...
This command was originally contributed by Martin Maisey <M.J.Maisey@webname.com>.
<! SQL qselect qhandle formvar [defaultval]
>
Creates a <SELECT> style list box for use in a form from
a query result. The first argument is the query handle, and the second has
the name of the form variable associated with the list box.
If the third argument is given, it acts as a default value for the list
box. That is, the line of HTML output for rows where the first column of
the query matches the third argument to qselect, the SELECTED
keyword is added to the <OPTION> tag.
The query result must contain at least two columns. The first column gives the values to be put in the form variable, and the second gives the labels for each value. The command is just a quicker way of doing the following:
<select name="formvar">qhandle
<! sql print_rows"<option name=\"@qhandle.0\">qhandle.1">
</select>
<! SQL seek qhandle pos
>
This command moves the row position of qhandle to row pos.
<! SQL set name
value >
This command sets the value of the variable name to value.
<! SQL setdefault name value
>
This command checks to see if variable name exists, and if it doesn't, sets it to value.
<! SQL setexpr name expression>
This command evaluates expression and assigns the value to the variable name.
<! SQL while expression>
...
<! SQL done>
This is a while loop. You should be able to work out how it works. You can put break and continue statements inside a while loop.
This function is not available with the old scanner code.
This section details some examples of using www-sql. The final example is a complete script that you may find useful for browsing a database.
Most of your scripts will look something like this:
<!-- HTML headers --> <!sql connect> <!sql database dbname> <!-- HTML code and www-sql statements --> <!sql close>
You can perform all types of SQL commands with the <!sql
query> command (after having connected and selected a
database). Here are some examples:
<!-- select all fields from first 10 records of table data -->
<!sql query "select * from data limit 10" q1>
<!-- display data in a table -->
<!sql qtable q1>
<!sql free q1>
<!-- delete all records from data where field1 equals the string value
of the variable var1 (escaping quotes in var1) -->
<!sql query "delete from data where field1 = '?var1'">
<!-- use the MySQL show command to describe the table data -->
<!sql query "show columns from data" q1>
<!sql qtable q1>
<!sql free q1>
<!-- insert some data into a table -->
<!sql query "insert into data (field1, field2) values
('Forty Two', 42)">
This example shows how you might set up a page to look at an telephone
directory table. This assumes that there is a table with three
columns (surname, firstname, number), called numbers in a database
telephone. It only shows a maximum of 10 numbers per page, and
lets you page through the results. It should be called
example.sql. The example only works with the MySQL version
of www-sql, since one of the queries uses a limit clause that is not
implemented in PostgreSQL yet. There is a version called example.pgsql that
uses some of the PostgreSQL features to emulate the behaviour using cursors.
<html> <head><title>Telephone Numbers</title></head> <body> <H1>Telephone Numbers</H1> <!-- Let user change query --> <form action=example.sql> <input name=sur > <input type=submit><br> </form> <! sql connect > <! sql database telephone > <! sql setdefault sur "-" > <! sql setdefault ofs 0 > <! sql query "select * from numbers where surname like '?sur' order by firstname limit $ofs,10" q1 > <! sql if $NUM_ROWS != 0 > <!-- Put in table --> <table> <tr> <th>Surname</th> <th>First Name</th> <th>Number</th> </tr> <! sql print_rows q1 "<tr> <td>@q1.0</td> <td>@q1.1</td> <td>@q1.2</td> </tr>\n" > </table> <!-- Put in navigation links --> <center> <! sql if 9 < $ofs > <! sql print "<a href=\"example.sql\?sur=#sur&ofs=" ><! sql eval $ofs - 10 ><! sql print "\">">Prev</a> <! sql else > Prev <! sql endif > <! sql if $NUM_ROWS = 10 > <! sql print "<a href=\"example.sql\?sur=#sur&ofs=" ><! sql eval $ofs + 10 ><! sql print "\">">Next</a> <! sql else > Next <! sql endif > </center> <! sql endif > <p> <center><em>Page produced by WWW-SQL</em></center> </body> </html>
This example gives an idea of what WWW-SQL can do. Of course it can also do simpler and more complex queries. If there is no way of doing the task, try e-mailing me at james@daa.com.au giving your idea, or even a patch.
I am currently a first year student at The University of Western Australia. I like sailing and programming. I distributed this program under the GPL because I thought others would find it useful. If you end up making lots of money from it, consider donating some to charity or me. If you just find it useful, please mail me at james@daa.com.au. This will make me more likely to release subsequent versions.
Also if you like fractals, try pointing a Java enabled browser at my fractals page.