|
WWW-SQLThis 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). ContentsIntroductionWWW-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>. CompilationI 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.
--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 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. Compiling for Multiple Database SystemsIn order to compile www-sql for multiple database systems, run configure as normal, but instead of running make with no parameters, run "make www-mysql" for a MySQL www-sql, and then run "make www-pgsql" for a PostgreSQL www-sql. These binaries will have the filename given in the argument to make.Installation
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
If you use Apache web server, there is an elegant way round this.
After installing the binary, edit your 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 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", ""); EOFand 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"); EOFand 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 SyntaxTag SyntaxThe tags recognised by WWW-SQL must be of the following form:
The meanings of the parameters after the
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 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.
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. 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
' Syntax Used in Command Definitions
In the definitions of the commands, required text will be in CommandsBuilt-in VariablesWhen WWW-SQL parses a document, it sets some extra variables at times. This is a list of those in use so far:
These variables can be accessed in the same way CGI variables may be. Notes on using CookiesWww-sql now supports cookies. When www-sql starts up, it reads in all cookies sent by the browser, and converts them to www-sql variables. If a form variable has the same name as a cookie, it takes precedence, preventing you from reading the cookie value.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> Break
This command breaks out of a print_loop or while loop. It is not available with the old scanner code. Close
This command closes the connection to the database backend. It should be called as the last WWW-SQL command. Connect
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.
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. Continue
This command skips the rest of a print_loop or while loop and continues. It is not available with the old scanner code. Convert
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
Database
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. Dumpvars
This command prints out the names and values of all www-sql variables set at the time of the call. Eval
This command evaluates expr, and inserts the result in the document. Exec
This command executes an external program, and inserts the output into
the document. It is only available if configure was run with the
This function was submitted by Malte John <malte@ddd.de>. Fetch
This command gets the next row from the query corresponding to qhandle. Free
This command frees the query handle qhandle. Ftime
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 If, Elsif, Else and Endif
[
[
These commands implement conditional execution. When the
The The elsif code was submitted by David J. N. Begley <d.begley@ieee.org>. Include
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
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.
This command expands variables in the argument text and includes the output in the document. Print_loop
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. Print_rows
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 Query
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 Qtable<! 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 This command was originally contributed by Martin Maisey <M.J.Maisey@webname.com>. Qlongform<! 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 This command was originally contributed by Martin Maisey <M.J.Maisey@webname.com>. Qselect<! SQL qselect qhandle formvar [defaultval]
>
Creates a 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 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:
Seek
This command moves the row position of qhandle to row pos. Set
This command sets the value of the variable name to value. Setdefault
This command checks to see if variable name exists, and if it doesn't, sets it to value. Setexpr
This command evaluates expression and assigns the value to the variable name. While
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. ExamplesThis section details some examples of using www-sql. The final example is a complete script that you may find useful for browsing a database. Structure Of A ScriptMost of your scripts will look something like this: <!-- HTML headers --> <!sql connect> <!sql database dbname> <!-- HTML code and www-sql statements --> <!sql close> Manipulating Data
You can perform all types of SQL commands with the <!-- 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)"> A Larger Example
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
<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. About the AuthorI 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. |