futureLAB JSP Tag Library - Sqlquery Tag

Marc Liyanage

Software Engineer
futureLAB AG

Table of Contents

Introduction

A note about security

Examples

A complete usage example
Using form values in queries

Output formats

xml
xml2
xhtml

Tag Attribute Reference

Introduction

$Id: tag_sqlquery.docbook,v 1.4 2003/07/26 11:57:53 mliyanage Exp $

This custom action is used to query an SQL database via a JDBC connection. The result set will be returned in an XML format. This is obviously very useful in combination with the <fl:style> tag, which can apply an XSL trasformation to the result set XML data.

With this combination of <fl:sqlquery> and <fl:style> it is possible to build database-driven sites quickly and with very little Java code.

A note about security

This tag is very useful in combination with form input. But you have to be very careful about taking form input and using it in an SQL query, because all sorts of bad things can happen if you don't check the input very carefully.

For example, someone could just append a string like "; DROP TABLE Customers;" to a form parameter. If you don't guard against this, the SQL command will be sent to your database and your Customers table will be gone for good.

For more information, check out these papers on SQL Injection:

Examples

A complete usage example

This first example shows everything that's needed to use the tag.


<%@ taglib
   uri="http://www.futurelab.ch/taglibs/futurelab-jsptags"
   prefix="fl"
%>

<%@ page import="java.sql.*" %>
<%@ page import="java.util.Properties" %>

<%

  Class.forName("org.postgresql.Driver");

  Properties dbProps = new Properties();
  dbProps.put("user", "postgres");
  dbProps.put("password", "www");
  dbProps.put("charSet", "ISO-8859-1");

  Connection conn = DriverManager.getConnection("jdbc:postgresql:test", dbProps);

%>


<fl:sqlquery connection="<%= conn %>" cdata="false">

	SELECT id, name FROM blah;

</fl:sqlquery>

Example 1. A complete example

Figure 1 shows how the XML result of the query would look like:


<resultset>

 <meta>
  <column><name>id</name></column>
  <column><name>name</name></column>
 </meta>

 <row>
  <id>1</id>
  <name>Marc</name>
 </row>

 <row>
  <id>2</id>
  <name>Joe</name>
 </row>

</resultset>

Figure 1. Result set XML Fragment

This will be inserted into the JSP page in place of the whole <fl:sqlquery> element.

Note how the DB query results were translated to XML: Each result row is returned in a <row> element. Within each row there are elements for each column. The element names are the same as the names of the corresponding columns in the DB, and the element contents are the values of the columns for each result row.

There is also a special <meta> element at the start that describes the result set. It currently contains the column names, which is convenient to build a table header row with XSL.

Other result formats are available, they are described in the section called “Output formats”.

Now let's use the style tag to apply an XSL transformation to this piece of XML data. Example 2 shows the XSL code.


<?xml version='1.0'?>
<xsl:stylesheet
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 version='1.0'
>


<xsl:template match="resultset">
  <html>
  <head><title>SQL Result</title></head>
  <body>

  <table border="1">
  <xsl:apply-templates/>
  </table>
  </body>
  </html>
</xsl:template>

<xsl:template match="meta">
  <tr><xsl:apply-templates/></tr>
</xsl:template>

<xsl:template match="column/name">
  <th><xsl:apply-templates/></th>
</xsl:template>

<xsl:template match="row">
  <tr><xsl:apply-templates/></tr>
</xsl:template>

<xsl:template match="id">
  <td><a href="anotherpage.jsp?id={.}"><xsl:apply-templates/></a></td>
</xsl:template>

<xsl:template match="name">
  <td><xsl:apply-templates/></td>
</xsl:template>

</xsl:stylesheet>

Example 2. A simple stylesheet

Example 3 shows the new version of the JSP code which refers to the XSL code.


<fl:style xsltfilename="/data/www/xsl/test.xsl">

  <fl:sqlquery connection="<%= conn %>" cdata="false">
  
	SELECT id, name FROM blah;
  
  </fl:sqlquery>

</fl:style>

Example 3. JSP code with style tag added.

Figure 2 shows how the result looks like in the browser.

Figure 2. The Result in a browser

Figure 3 shows the HTML code that was generated (reformatted for brevity).


<html>
<head><title>SQL Result</title></head>

  <body>
  
  <table border="1">
  
  <tr><th>id</th><th>name</th></tr>
  
  <tr><td><a href="detail.jsp?id=1">1</a></td><td>Marc</td></tr>
  <tr><td><a href="detail.jsp?id=2">2</a></td><td>Joe</td></tr>
  
  </table>

</body>
</html>

Figure 3. The resulting HTML code.

Notice how the id was used to construct a link to another JSP. That other page could be a detail view, which we'll build in the next section.

Using form values in queries

Example 4 shows the JSP code for a detail page which uses a request parameter in the SQL query. This is for illustration only, as noted in the section called “A note about security” you should not do it this way in a productive system.


<fl:style xsltfilename="/data/www/xsl/detail.xsl">

  <fl:sqlquery connection="<%= conn %>" cdata="false">
  
	SELECT id, name, value FROM blah WHERE id = <%= request.getParameter("id") %>;
  
  </fl:sqlquery>

</fl:style>

Example 4. JSP code for a detail page.

Figure 4 shows how the XML result of this second query would look like:


<resultset>

 <meta>
  <column><name>id</name></column>
  <column><name>name</name></column>
  <column><name>value</name></column>
 </meta>

 <row>
  <id>2</id>
  <name>Joe</name>
  <value>33</value>
 </row>

</resultset>

Figure 4. Result set XML Fragment

There is obviously only one result row here because we asked for it with the WHERE clause in the SQL statement.

Again, we need some XSL code to transform this into something useful, in this case the detail edit form for this particular result row. The detail.xsl file referenced in the JSP snippet above could contain these templates:


<xsl:template match="resultset">
<html><head><title>SQL Detail</title></head>
<body>
  <form action="save.jsp">
  <table border="0" cellspacing="1" bgcolor="#dddddd">
	<xsl:apply-templates select="row" />
  </table>
  </form>
  </body>
  </html>
</xsl:template>

<xsl:template match="row">
  <xsl:apply-templates/>
  <tr><td colspan="2"><hr/></td></tr>
  <tr><td></td><td><input type="submit" value="Submit"/></td></tr>
</xsl:template>

<xsl:template match="id">
  <input type="hidden" name="id" value="{.}"/>
</xsl:template>

<xsl:template match="name">
  <tr><th>name</th><td><input type="text" name="name" value="{.}"/></td></tr>
</xsl:template>

<xsl:template match="value">
  <tr><th>value</th><td><input type="text" name="value" value="{.}"/></td></tr>
</xsl:template>

Figure 5. XSL code for the detail page.

Figure 6 shows how the resulting detail page looks like in the browser.

Figure 6. The detail page in a browser

Figure 7 shows the HTML code.


<html><head><title>SQL Detail</title></head>
<body>
<form action="save.jsp">

<table bgcolor="#dddddd" cellspacing="1" border="0">
<input value="2" name="id" type="hidden">
<tr><th>name</th><td><input value="Joe" name="name" type="text"></td></tr>
<tr><th>value</th><td><input value="33" name="value" type="text"></td></tr>
<tr><td colspan="2"><hr></td></tr>
<tr><td></td><td><input value="Submit" type="submit"></td></tr>
</table>

</form>
</body>
</html>

Figure 7. The detail page HTML code.

This code references yet another page called save.jsp. That page could for example take the form parameters and issue an UPDATE statement for the record with the new data and then redirect to the first page with the list view again.

This is left as an exercise for the reader...

Output formats

The XML result set is available in several formats. They are selected with the outputmode attribute of the <fl:sqlquery> tag.

xml

This is the default and the one used in the examples above.


<resultset>
<meta>
<column><name>id</name></column>
<column><name>name</name></column>
</meta>
<row>
<id>1</id>
<name>Marc</name>
</row>
<row>
<id>2</id>
<name>Joe</name>
</row>
</resultset>

Figure 8. The xml output format.

xml2

This is slightly different in that the names containing the column values are all the same (<field>), which might make matching with XSL easier in some cases. The column names are still present, this time as attributes.


<resultset>
<meta>
<column><name>id</name></column>
<column><name>name</name></column>
</meta>
<row>
<field name="id">1</field>
<field name="name">Marc</field>
</row>
<row>
<field name="id">2</field>
<field name="name">Joe</field>
</row>
</resultset>

Figure 9. The xml2 output format.

xhtml

This creates an XHTML table. Could be output as HTML table with a very simple stylesheet containing an <xsl:copy-of> instruction.


<table border="1">
<tr>
<th>id</th>
<th>name</th>
</tr>
<tr>
<td>1</td>
<td>Marc</td>
</tr>
<tr>
<td>2</td>
<td>Joe</td>
</tr>
</table>

Figure 10. The xhtml output format.

Tag Attribute Reference

This section provides a detailed list of the various attributes for this tag.

All of them accept run-time values, i.e. you can, and usually have to, use the <%= ... %> construct in the attribute value.

Attribute name	Description	Default	Mandatory
`connection`	This is the most important parameter. It is a JDBC connection object which you have to set up yourself before giving it to the tag.	`-`	Yes
`cdata`	Usually, all values returned from the database will be wrapped within CDATA sections so you won't end up with illegal XML syntax if the values happen to contain characters like <, > or &. If you know that this won't happen with your data, then you can switch off these CDATA wrappers by setting this attribute to `false`	`true`	No
`columnnames`	The names of the columns in the database end up in the XML data, either as element names or attribute values. The tag gets these names from the database automatically. In some cases it can be useful to override the names coming from the database, e.g. when using a join and several columns end up having the same name. This might complicate matching in XSL code. You can either control the column names directly in the SQL statement using `SELECT a.b AS b1, b.b as b2`, or you can use this optional attribute to set the desired element names explicitly. The names have to appear in the order in which the fields will be returned by the database, the number of names has to match the number of columns, and the names have to be separated by commans and optional whitespace. Example: `columnames``="ID, Name, Value"`.	taken from JDBC connection	No
`outputmode`	The format of the XML data, as described in the section called “Output formats”.	`xml`	No

Table 1. Attributes for the sqlquery tag