BUG #2250: JSTL parameterized queries inserting numeric values - Mailing list pgsql-bugs

From Ian Moore
Subject BUG #2250: JSTL parameterized queries inserting numeric values
Date
Msg-id 20060209112646.50585F0AC7@svr2.postgresql.org
Whole thread Raw
Responses Re: BUG #2250: JSTL parameterized queries inserting numeric
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: "Satheesh"
Date:
Subject: BUG #2249: unsupported frontend protocol
Next
From: "Fix for terminal server"
Date:
Subject: BUG #2248: Fix for terminal server