Re: Prepared Statements - Mailing list pgsql-jdbc

Kim Ho wrote:

>To speed things up a bit, since the regoutParam patch is not likely to
>be approved anytime soon.
>
>This patch
>- adds single quotes for numbers in setObject and also setInt/Byte/etc.
>- Improves getInt/Long when you may have parser errors if you're too
>close to Integer.MIN_VALUE or Integer.MAX_VALUE. Thanks to Fujitsu.
>- Improves radix point handling when using setObject to an integer
>parameter while passing in a float. This is especially important in
>callable statements.
>
I see :-)
Aside from taking away that ability to be able to pass sets using
setObject(), which is unfortunate, about the only improvement this makes
seems to be that the malicious "injector" would have to pass in a string
like (just making sure it doesn't contain any dots :-)

1';delete from precious_table where 'true

to make a statement like

select * from somewhere where id=?

to get sent as "select * from somewhere where id='1';delete from
precious_table where 'true'" and wipe out your precious table :-)


You really believe you can win this race, by plugging this particular
hole, I am afraid, you are going to have to always parse the input
that,s supposed to be numerical into a number...


Dima

P.S. On a different note, something like
"select ?"
setString (1, "\047");

returns "\047" when executed. Now *this*, is a bug - because it is
supposed to return a string, containing a quote as a single character...






>
>Cheers,
>
>Kim
>
>On Fri, 2003-07-18 at 12:51, Fernando Nasser wrote:
>
>
>>Barry Lind wrote:
>>
>>
>>>Dmitry,
>>>
>>>That is a bug.  Thanks for pointing it out.  Anyone care to submit a patch?
>>>
>>>
>>>
>>Kim's patch fixes this.  It is pending approval.
>>
>>
>>
>>--
>>Fernando Nasser
>>Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
>>2323 Yonge Street, Suite #300
>>Toronto, Ontario   M4P 2C9
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>
>
>
>
>------------------------------------------------------------------------
>
>? temp.diff
>Index: org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
>===================================================================
>RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java,v
>retrieving revision 1.13
>diff -c -p -r1.13 AbstractJdbc1ResultSet.java
>*** org/postgresql/jdbc1/AbstractJdbc1ResultSet.java    30 Jun 2003 21:10:55 -0000    1.13
>--- org/postgresql/jdbc1/AbstractJdbc1ResultSet.java    18 Jul 2003 17:02:20 -0000
>*************** public abstract class AbstractJdbc1Resul
>*** 805,811 ****
>              try
>              {
>                  s = s.trim();
>!                 return Integer.parseInt(s);
>              }
>              catch (NumberFormatException e)
>              {
>--- 805,811 ----
>              try
>              {
>                  s = s.trim();
>!                 return Float.valueOf(s).intValue();
>              }
>              catch (NumberFormatException e)
>              {
>*************** public abstract class AbstractJdbc1Resul
>*** 822,828 ****
>              try
>              {
>                  s = s.trim();
>!                 return Long.parseLong(s);
>              }
>              catch (NumberFormatException e)
>              {
>--- 822,828 ----
>              try
>              {
>                  s = s.trim();
>!                 return Double.valueOf(s).longValue();
>              }
>              catch (NumberFormatException e)
>              {
>Index: org/postgresql/jdbc1/AbstractJdbc1Statement.java
>===================================================================
>RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
>retrieving revision 1.27
>diff -c -p -r1.27 AbstractJdbc1Statement.java
>*** org/postgresql/jdbc1/AbstractJdbc1Statement.java    9 Jul 2003 05:12:04 -0000    1.27
>--- org/postgresql/jdbc1/AbstractJdbc1Statement.java    18 Jul 2003 17:02:22 -0000
>*************** public abstract class AbstractJdbc1State
>*** 920,926 ****
>       */
>      public void setByte(int parameterIndex, byte x) throws SQLException
>      {
>!         bind(parameterIndex, Integer.toString(x), PG_TEXT);
>      }
>
>      /*
>--- 920,926 ----
>       */
>      public void setByte(int parameterIndex, byte x) throws SQLException
>      {
>!         bind(parameterIndex, "'" + Integer.toString(x) + "'", PG_TEXT);
>      }
>
>      /*
>*************** public abstract class AbstractJdbc1State
>*** 933,939 ****
>       */
>      public void setShort(int parameterIndex, short x) throws SQLException
>      {
>!         bind(parameterIndex, Integer.toString(x), PG_INT2);
>      }
>
>      /*
>--- 933,939 ----
>       */
>      public void setShort(int parameterIndex, short x) throws SQLException
>      {
>!         bind(parameterIndex, "'" + Integer.toString(x) + "'" , PG_INT2);
>      }
>
>      /*
>*************** public abstract class AbstractJdbc1State
>*** 946,952 ****
>       */
>      public void setInt(int parameterIndex, int x) throws SQLException
>      {
>!         bind(parameterIndex, Integer.toString(x), PG_INTEGER);
>      }
>
>      /*
>--- 946,952 ----
>       */
>      public void setInt(int parameterIndex, int x) throws SQLException
>      {
>!         bind(parameterIndex, "'" + Integer.toString(x) + "'", PG_INTEGER);
>      }
>
>      /*
>*************** public abstract class AbstractJdbc1State
>*** 959,965 ****
>       */
>      public void setLong(int parameterIndex, long x) throws SQLException
>      {
>!         bind(parameterIndex, Long.toString(x), PG_INT8);
>      }
>
>      /*
>--- 959,965 ----
>       */
>      public void setLong(int parameterIndex, long x) throws SQLException
>      {
>!         bind(parameterIndex, "'" + Long.toString(x) + "'", PG_INT8);
>      }
>
>      /*
>*************** public abstract class AbstractJdbc1State
>*** 972,978 ****
>       */
>      public void setFloat(int parameterIndex, float x) throws SQLException
>      {
>!         bind(parameterIndex, Float.toString(x), PG_FLOAT);
>      }
>
>      /*
>--- 972,978 ----
>       */
>      public void setFloat(int parameterIndex, float x) throws SQLException
>      {
>!         bind(parameterIndex, "'" + Float.toString(x) + "'", PG_FLOAT);
>      }
>
>      /*
>*************** public abstract class AbstractJdbc1State
>*** 985,991 ****
>       */
>      public void setDouble(int parameterIndex, double x) throws SQLException
>      {
>!         bind(parameterIndex, Double.toString(x), PG_DOUBLE);
>      }
>
>      /*
>--- 985,991 ----
>       */
>      public void setDouble(int parameterIndex, double x) throws SQLException
>      {
>!         bind(parameterIndex, "'" + Double.toString(x) + "'", PG_DOUBLE);
>      }
>
>      /*
>*************** public abstract class AbstractJdbc1State
>*** 1003,1009 ****
>              setNull(parameterIndex, Types.DECIMAL);
>          else
>          {
>!             bind(parameterIndex, x.toString(), PG_NUMERIC);
>          }
>      }
>
>--- 1003,1009 ----
>              setNull(parameterIndex, Types.DECIMAL);
>          else
>          {
>!             bind(parameterIndex, "'" + x.toString() + "'", PG_NUMERIC);
>          }
>      }
>
>*************** public abstract class AbstractJdbc1State
>*** 1464,1486 ****
>          switch (targetSqlType)
>          {
>              case Types.INTEGER:
>-                 if (x instanceof Boolean)
>-                     bind(parameterIndex,((Boolean)x).booleanValue() ? "1" :"0", PG_BOOLEAN);
>-                 else
>-                     bind(parameterIndex, x.toString(), PG_INTEGER);
>-                 break;
>              case Types.TINYINT:
>              case Types.SMALLINT:
>              case Types.BIGINT:
>              case Types.REAL:
>              case Types.FLOAT:
>              case Types.DOUBLE:
>              case Types.DECIMAL:
>              case Types.NUMERIC:
>!                 if (x instanceof Boolean)
>!                     bind(parameterIndex, ((Boolean)x).booleanValue() ? "1" : "0", PG_BOOLEAN);
>!                 else
>!                     bind(parameterIndex, x.toString(), PG_NUMERIC);
>                  break;
>              case Types.CHAR:
>              case Types.VARCHAR:
>--- 1464,1484 ----
>          switch (targetSqlType)
>          {
>              case Types.INTEGER:
>              case Types.TINYINT:
>              case Types.SMALLINT:
>+                 x = removeRadix(x,Types.INTEGER);
>+                 bindNumber(parameterIndex,x,PG_INTEGER);
>+                 break;
>              case Types.BIGINT:
>+                 x = removeRadix(x,Types.BIGINT);
>+                 bindNumber(parameterIndex,x,PG_INT8);
>+                 break;
>              case Types.REAL:
>              case Types.FLOAT:
>              case Types.DOUBLE:
>              case Types.DECIMAL:
>              case Types.NUMERIC:
>!                 bindNumber(parameterIndex,x,PG_NUMERIC);
>                  break;
>              case Types.CHAR:
>              case Types.VARCHAR:
>*************** public abstract class AbstractJdbc1State
>*** 2026,2031 ****
>--- 2024,2056 ----
>          if (parameterIndex != 1)
>              throw new PSQLException("postgresql.call.noinout");
>      }
>+
>+     private void bindNumber(int parameterIndex, Object x, String pgtype) throws SQLException
>+     {
>+         if (x instanceof Boolean)
>+             bind(parameterIndex,((Boolean)x).booleanValue() ? "'1'" :"'0'", pgtype);
>+         else
>+             bind(parameterIndex, "'"+x.toString()+"'", pgtype);
>+     }
>+
>+
>+     private Object removeRadix(Object x, int sqlType)
>+     {
>+         if (x.toString().indexOf(".")>0)
>+         {
>+             switch (sqlType)
>+             {
>+                 case Types.BIGINT:
>+                     x = String.valueOf(Double.valueOf(x.toString()).longValue());
>+                     break;
>+                 default:
>+                     x = String.valueOf(Float.valueOf(x.toString()).intValue());
>+                     break;
>+             }
>+         }
>+         return x;
>+     }
>+
>
>
>
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>