You are viewing a plain text version of this content. The canonical link for it is here.
Posted to taglibs-user@tomcat.apache.org by John Hicks <ja...@gulfbridge.net> on 2002/06/21 20:21:18 UTC
JSTL SQL updates: escaping quotes, default values, form validation, and efficiency
Hi All--
I'm having problems with what I thought was going to be a
very simple project and, after trying my best, find I must
now swallow my pride and *plead* for help.
The "simple" project: Updating a single SQL table using an
online form with fields that correspond exactly to those of
the table (i.e. no business logic). The same form serves
for both input and display of the data. There are about 150
fields in the form and table.
The problem: Depending on which approach I use, I get bad
data in my database, errors doing an update, extremely slow
execution of an update, and/or a complete blow-up of Java
and Tomcat.
My configuration:
RedHat 7.2
J2SDK1.4.0
Jakarta-Tomcat-4.0.3
JSTL 1.0 Release Candiate 1
PostgreSQL-7.1.3
The approaches I've tried and the problems they've given me:
1. The KISS approach
I first tried a simple:
UPDATE programs
SET org = '<c:out value="${param.org}" default="" />',
name = '<c:out value="${param.name}" default="" />',
. . . .
WHERE progKey = '<c:out value="${param.progCode}" />'
Using the <c:out> tag has the beauty of allowing me to set
default values for any undefined fields. The tag also
defaults to escapeXml="true" which escapes any single
quotes in the data which would otherwise prematurely end
the quoted string.
This approach works great and is superfast -- virtually
instantaneous (and this for an update of all 150 fields in
the record!).
The problem is that the escapeXml mechanism is really
designed for data flowing in the other direction (from a
database to a web page). The single quote and other
characters (double-quotes, angle brackets, and ampersand)
are encoded into ampersand-escaped "character-entity" codes
designed for HTML output. This poses a slight problem when
I output the data into an HTML form text field, where the
escape coding is displayed literally. It poses a greater
problem when that same data is stored a second time and all
the ampersands in the previous escapeXml encoding are
escaped again (eventually resulting in &amp;amp;...).
Conclusion: We need an "escapeSql" attribute for the c:out
tag. This would double any single quotes in the data.
2. The "right" way.
After consulting the archives for this group, I realized
that I should be using the <param> tag with my updates to
handle the embedded single quotes in my data.
UPDATE programs
SET org = ?,
name = ?,
. . . .
WHERE progKey = ?
<sql:param value="${param.org}" />
<sql:param value="${param.name}" />
. . .
<sql:param value="${param.progCode}" />
Rather than fix the problem, this created
NullPointerExceptions when I performed an update:
: Failed to store object - Exception:
java.lang.NullPointerException
Stack Trace:
java.lang.NullPointerException at
org.postgresql.Connection.putObject(Connection.java:790)
at
org.postgresql.jdbc2.PreparedStatement.setObject(PreparedStatement.java:596)
at
org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.setParameters(UpdateTagSupport.java:285)
at
org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport.doEndTag(UpdateTagSupport.java:192)
at
I finally traced this problem to undefined request
parameter fields, specifically radio buttons and check
boxes (i.e. whenever a check box was left unchecked or when
the user didn't select any of a set of radio buttons).
Observation: The error message is pretty cryptic. Any way
the name of the undefined parameter in question could be
displayed?
Conclusion: The param tag should have a default attribute
similar to the c:out tag.
2a. Updating in smaller bites.
Somewhere in debugging the above, I started blowing up the
JVM with each update:
# HotSpot Virtual Machine Error, Internal Error
# Please report this error at
# http://java.sun.com/cgi-bin/bugreport.cgi
#
# Java VM: Java HotSpot(TM) Client VM (1.4.0-b92 mixed mode)
#
# Error ID: 47454E45524154452F4F502D41500E4350500848
#
# Problematic Thread: prio=1 tid=0x0x89461f8 nid=0xd2b
runnable
I solved this by breaking my update into two statements.
(I'm updating about 150 fields in the table.) I'm thinking
the problem may be due to my memory (256M) or perhaps a
limitation in JSTL (maybe 128 fields?). I had no problem in
updating all 150 fields using the KISS method (No. 1 above).
At any rate, I can live with this workaround.
3. Form validation via JSP
Clearly I had to validate the checkbox and radio button
fields to ensure they were defined before proceeding with
my update. So I added statements like the following (which
is validation for a checkbox named "gym"):
<c:set var="gym" value="${param.gym}" />
<c:if test="${empty gym}" >
<c:set var="gym" value="No" />
</c:if>
(And I changed the corresponding param statement to refer
to "gym" instead of "param.gym")
I repeated this 16 times , once for each checkbox and set
of radio buttons in my form.
This works.
The only problem is that an update that previously took
less than one second now takes 55 seconds! Not a good thing.
I hope I am doing something terribly wrong with my
validation. Someone please tell me that JSP tags are not
inherently this slow.
Summarizing:
Conclusion: We need an "escapeSql" attribute for the c:out
tag. This would double any single quotes in the data.
Observation: The error message for a NullPointerException
is pretty cryptic. Any way the name of the undefined
parameter in question could be displayed?
Conclusion: The param tag should have a default attribute
similar to the c:out tag.
Big problem: An update that took one second has gone to 55
seconds by adding param tags and validation of 16 fields.
Am I doing something terribly wrong with my validation?
My configuration:
RedHat 7.2
J2SDK1.4.0
Jakarta-Tomcat-4.0.3
JSTL 1.0 Release Candiate 1
PostgreSQL-7.1.3
Any help, advice, observations, etc. would be greatly
appreciated. TIA.
--John Hicks
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: JSTL SQL updates: escaping quotes, default values, form
validation, and efficiency
Posted by Paul DuBois <pa...@snake.net>.
At 15:50 -0400 6/21/02, Shawn Bayern wrote:
>On Fri, 21 Jun 2002, John Hicks wrote:
>
>> On Friday 21 June 2002 03:09 pm, you wrote:
>> > > I finally traced this problem to undefined request
>> > > parameter fields, specifically radio buttons and check
>> > > boxes (i.e. whenever a check box was left unchecked or
>> > > when the user didn't select any of a set of radio
>> > > buttons).
>> >
>> > Actually, this looks like a bug in our implementation of
>> > <sql:param>; a null 'value' should cause the
>> > corresponding column to be set to SQL NULL.
>>
>> In my case, the response parameter is not defined at all.
>> (Apparently this is an attribute of checkboxes in HTML forms.) Should
>> an undefined attribute produce the same results as a defined attribute
>> with a null value?
>
>Yeah - there's no difference, formally in the Servlet API, between an
>undefined parameter and a parameter with no value. That is, if
>request.getParameter("foo") returns null, that means that the parameter
>doesn't exist. Whether it doesn't exist because a browser didn't set it
>or because it was never in a form isn't a detail that's accessible to a
>servlet or JSP page.
>
>Note that there *is* a difference between "" and null, which was the main
>reason we introduced the 'empty' keyword. (That is, we wanted to provide
>page authors with a way to combine the two cases, since most of the time,
>you don't care whether a parameter is "" or null.)
Except that, for database programming, sometimes you *do* care very
much.
>
>--
>Shawn Bayern
>"JSTL in Action" http://www.jstlbook.com
>(coming in July 2002 from Manning Publications)
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: JSTL SQL updates: escaping quotes, default values, form validation,
and efficiency
Posted by Shawn Bayern <ba...@essentially.net>.
On Fri, 21 Jun 2002, John Hicks wrote:
> On Friday 21 June 2002 03:09 pm, you wrote:
> > > I finally traced this problem to undefined request
> > > parameter fields, specifically radio buttons and check
> > > boxes (i.e. whenever a check box was left unchecked or
> > > when the user didn't select any of a set of radio
> > > buttons).
> >
> > Actually, this looks like a bug in our implementation of
> > <sql:param>; a null 'value' should cause the
> > corresponding column to be set to SQL NULL.
>
> In my case, the response parameter is not defined at all.
> (Apparently this is an attribute of checkboxes in HTML forms.) Should
> an undefined attribute produce the same results as a defined attribute
> with a null value?
Yeah - there's no difference, formally in the Servlet API, between an
undefined parameter and a parameter with no value. That is, if
request.getParameter("foo") returns null, that means that the parameter
doesn't exist. Whether it doesn't exist because a browser didn't set it
or because it was never in a form isn't a detail that's accessible to a
servlet or JSP page.
Note that there *is* a difference between "" and null, which was the main
reason we introduced the 'empty' keyword. (That is, we wanted to provide
page authors with a way to combine the two cases, since most of the time,
you don't care whether a parameter is "" or null.)
--
Shawn Bayern
"JSTL in Action" http://www.jstlbook.com
(coming in July 2002 from Manning Publications)
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: JSTL SQL updates: escaping quotes, default values, form validation, and efficiency
Posted by John Hicks <ja...@gulfbridge.net>.
Thanks for your superfast reponse!!
I'll be happy to forward you my JSP etc. (I'm not fast as
you, so give me a few more minutes!!)
In the meantime, one point to ponder:
On Friday 21 June 2002 03:09 pm, you wrote:
> > I finally traced this problem to undefined request
> > parameter fields, specifically radio buttons and check
> > boxes (i.e. whenever a check box was left unchecked or
> > when the user didn't select any of a set of radio
> > buttons).
>
> Actually, this looks like a bug in our implementation of
> <sql:param>; a null 'value' should cause the
> corresponding column to be set to SQL NULL.
In my case, the response parameter is not defined at all.
(Apparently this is an attribute of checkboxes in HTML
forms.) Should an undefined attribute produce the same
results as a defined attribute with a null value?
TOA
--John Hicks
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: JSTL SQL updates: escaping quotes, default values, form validation,
and efficiency
Posted by Shawn Bayern <ba...@essentially.net>.
Hi John,
Thanks for your detailed message! Some responses:
On Fri, 21 Jun 2002, John Hicks wrote:
> 1. The KISS approach
>
> I first tried a simple:
>
> UPDATE programs
> SET org = '<c:out value="${param.org}" default="" />',
> name = '<c:out value="${param.name}" default="" />',
> . . . .
> WHERE progKey = '<c:out value="${param.progCode}" />'
> [...]
>
> Conclusion: We need an "escapeSql" attribute for the c:out
> tag. This would double any single quotes in the data.
However, since escaping metacharacters is really best left to he JDBC
driver, the right way in all cases is to use PreparedStatements, which
JSTL exposes through <sql:query>, <sql:update>, and <sql:param>. An
'escapeSql' attribute would never have an appropriate use.
Thus, it's good that you tried the next approach... :-)
> 2. The "right" way.
>
> After consulting the archives for this group, I realized
> that I should be using the <param> tag with my updates to
> handle the embedded single quotes in my data.
>
> UPDATE programs
> SET org = ?,
> name = ?,
> . . . .
> WHERE progKey = ?
>
> <sql:param value="${param.org}" />
> <sql:param value="${param.name}" />
> . . .
> <sql:param value="${param.progCode}" />
Indeed, this looks right.
> Rather than fix the problem, this created
> NullPointerExceptions when I performed an update:
>
> : Failed to store object - Exception:
> java.lang.NullPointerException
> Stack Trace:
> java.lang.NullPointerException at
> org.postgresql.Connection.putObject(Connection.java:790)
>
> I finally traced this problem to undefined request
> parameter fields, specifically radio buttons and check
> boxes (i.e. whenever a check box was left unchecked or when
> the user didn't select any of a set of radio buttons).
Actually, this looks like a bug in our implementation of <sql:param>; a
null 'value' should cause the corresponding column to be set to SQL NULL.
If you can replicate the problem, please consider filing a bug report at
http://nagoya.apache.org/bugzilla. In the meantime, we'll look at it more
closely and, if necessary, fix the problem in the latest code in CVS.
> Observation: The error message is pretty cryptic. Any way
> the name of the undefined parameter in question could be
> displayed?
This may be addressed implicitly if the behavior you're experiencing is
indeed a bug; if not, you might want to file a separate feature request
(RFE) to improve the error message. (This would involve wrapping the
exception produced by the driver and adding our own descriptive text.)
> Conclusion: The param tag should have a default attribute similar to
> the c:out tag.
I don't believe this conclusion follows, since the JSTL spec explicitly
says, "If [the] value [attribute] is null, the parameter is set to the SQL
value NULL." You could always use <c:out> within a <sql:param> tag if you
wanted to avail yourself of its ability to produce default values; we have
so far avoided giving multiple tags this ability to reduce overall
complexity in JSTL. That is, if you want to use a "default value" for
<sql:param>, you can already write
<sql:param>
<c:out value="..." default="..." />
</sql:param>
Since this case shouldn't be too common (after all, many database support
default values of their own when a column is set to NULL), I'm not
inclined to think future versions of JSTL should add this feature.
> 2a. Updating in smaller bites.
>
> Somewhere in debugging the above, I started blowing up the
> JVM with each update:
> # HotSpot Virtual Machine Error, Internal Error
> # Please report this error at
> # http://java.sun.com/cgi-bin/bugreport.cgi
> #
> # Java VM: Java HotSpot(TM) Client VM (1.4.0-b92 mixed mode)
> #
> # Error ID: 47454E45524154452F4F502D41500E4350500848
> #
> # Problematic Thread: prio=1 tid=0x0x89461f8 nid=0xd2b
> runnable
>
> I solved this by breaking my update into two statements.
> (I'm updating about 150 fields in the table.) I'm thinking
> the problem may be due to my memory (256M) or perhaps a
> limitation in JSTL (maybe 128 fields?). I had no problem in
> updating all 150 fields using the KISS method (No. 1 above).
>
> At any rate, I can live with this workaround.
I can't speak authoritatively about this; this indeed looks like a JVM
bug, though it's hard to say (from this information alone) what triggered
it.
> 3. Form validation via JSP
>
> Clearly I had to validate the checkbox and radio button
> fields to ensure they were defined before proceeding with
> my update. So I added statements like the following (which
> is validation for a checkbox named "gym"):
>
> <c:set var="gym" value="${param.gym}" />
> <c:if test="${empty gym}" >
> <c:set var="gym" value="No" />
> </c:if>
>
> (And I changed the corresponding param statement to refer
> to "gym" instead of "param.gym")
>
> I repeated this 16 times , once for each checkbox and set
> of radio buttons in my form.
>
> This works.
>
> The only problem is that an update that previously took
> less than one second now takes 55 seconds! Not a good thing.
>
> I hope I am doing something terribly wrong with my
> validation. Someone please tell me that JSP tags are not
> inherently this slow.
Indeed, it's not in line with our experience that 16 checks should take 55
seconds; it sounds like there's a local problem. (My test environment,
indicentally, is very similar to yours; I use RedHat, Postgres, and Tomcat
4. So I don't think there's anything wrong with the *choice* of
environment -- it sounds like a local configuration or coding issue.)
Can you send us the actual JSP code you're using? The problem might be
there, or it might lie in something else in your local environment.
--
Shawn Bayern
"JSTL in Action" http://www.jstlbook.com
(coming in July 2002 from Manning Publications)
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>