The following bug has been logged online:
Bug reference: 2250
Logged by: Ian Moore
Email address: ian.moore@ism-online.co.uk
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description: JSTL parameterized queries inserting numeric values
Details:
When using the JSTL via JDBC, there is the option to write INSERT/UPDATE
statements with parameters in JSP's.
There is only two types the data parameters can be, a date or a character
varying string.
In most databases, if a character varying string is provided that contains a
numeric value, and this is used to insert/update a numeric field, the driver
will attempt a type conversion to the numeric value of the string and report
errors only if the string is not a valid . However trying this in JSTL,
which only provides support for text or date parameters results in the
following error:
ERROR: column "????" is of type integer but expression is of type character
varying
For the example I am trying, the following table and JSP/JSTL snippet were
used:
-----
create table state_defns(state integer primary key,
description varchar(200));
-----
<sql:update>
INSERT INTO state_defns (state, description)
VALUES (?,?);
<sql:param value='${param.state}'/>
<sql:param value='${param.name}'/>
</sql:update>
-----
I have noticed other people trying to do this and have had the same error,
though some have suggested it worked at version 7.1
I have also tried it on version 8.1 (database and jdbc drivers) on linux.
I have used this as a workaround:
<sql:update>
INSERT INTO state_defns (state, description) VALUES (${param.state},?);
<sql:param value='${param.name}'/>
</sql:update>
but this poses too many security risks from SQL Injection.
I believe this issue is also true for JDBC
