Skip site navigation (1) Skip section navigation (2)

BUG #2250: JSTL parameterized queries inserting numeric values

From: "Ian Moore" <ian(dot)moore(at)ism-online(dot)co(dot)uk>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2250: JSTL parameterized queries inserting numeric values
Date: 2006-02-09 11:26:46
Message-ID: 20060209112646.50585F0AC7@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2250
Logged by:          Ian Moore
Email address:      ian(dot)moore(at)ism-online(dot)co(dot)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

Responses

pgsql-bugs by date

Next:From: Michael FuhrDate: 2006-02-09 12:39:50
Subject: Re: Please get me out of this ASAP
Previous:From: Michael MeskesDate: 2006-02-09 08:14:56
Subject: Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group