Re: [INTERFACES] New code for JDBC driver - Mailing list pgsql-jdbc

I think we will allow people to use it if they feel it is useful but not
apply it to the main code.

> Hi
>
> Sorry about not supplying a proper patch when asked for it and for not
> replying until today... Didn't check my mail on Sun.
>
> The 'patch' is just a hack I cooked up to spot and flag SQL statements with
> semicolons *outside* quotes and double qoutes. If such a semicolon is found,
> an SQLException is thrown.
>
> I need to have the queries checked for stuff like this in order to stop
> people entering malicious sql queries. The quotes are escaped seperatly
> outside the JDBC driver.
>
> I have no clue where to place the code and escapeSQL looked as good a place
> as any
> :-P It gets called by default on every query and so it just right for us.
>
> Once again let me stress that it was not my intention to submit an official
> patch. This is a hack which works for me and seeing as the driver doesn't
> handle semicolons outside queries, I though other people might want to use
> the code.
>
> Actually, escapeSQL is supposed to escape the quotes in an SQL query but it
> doesn't (I checked an older version, there was only one statement in there,
> return sql;!
> The newer version strips out ODBC date stuff, but still no quote handling).
> I went through the archives, I think some one asked for this in 1997 and it
> was promised for version 6.6 of the driver :-P
>
> I think you can see why we got a little impatient :-D
>
> if(code.equals("useful"))
>     use();
> else
>     dump();
>
> --Arsalan.
>
> BTW, the two System.out.println()'s were just there for debugging... I
> forgot to remove them. Sorry.
>
>
>
> ----- Original Message -----
> From: Barry Lind <barry@xythos.com>
> To: PostgreSQL jdbc list <pgsql-jdbc@postgresql.org>
> Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Arsalan Zaidi
> <azaidi@directi.com>
> Sent: Sunday, July 01, 2001 6:49 AM
> Subject: Re: [INTERFACES] New code for JDBC driver
>
>
> >
> > Arsalan,
> >
> > I am having a hard time understanding what this patch is supposed to do.
> > What do you mean by "look for semicolons outside '' and ""'s"? It is
> > valid in postgresql to have semicolons outside quotes, so I don't
> > understand what you are trying to do.
> >
> > After looking at the JDBC spec on what escape processing is supposed to
> > do (section 11 in the jdbc 1.2 spec), it is intended to be a database
> > independent way to specify certain constructs that are not yet
> > implemented in a standard way across databases. All of these special
> > constructs are of the form: {name parameters}
> > For example {d '2001-06-30'} is a way to specify a date value regardless
> > of the underlying databases date format. Apparently the JDBC spec uses
> > the same logic as is defined by ODBC for the same purposes.
> >
> > thanks,
> > --Barry
> >
> >
> > Bruce Momjian wrote:
> >
> > >>No response for my query since yesterday.
> > >>
> > >>I've changed the code in Connection.escapeSQL() to look for semicolons
> > >>outside '' and ""'s.
> > >>
> > >>It's not been well tested, but it seems to work. Someone might want to
> add
> > >>it to the source tree, or to their own local versions if they wish.
> > >>
> > >>
> > >
> > > OK, here is the diff, I think.  Can jdbc people comment on it?
> > >
> > >
> > >
> > > ------------------------------------------------------------------------
> > >
> > > *** Connection.java Wed Jun  6 20:09:32 2001
> > > --- /bjm/x Sat Jun 30 12:35:39 2001
> > > ***************
> > > *** 931,937 ****
> > > --- 21,123 ----
> > >           index = sql.indexOf("{d");
> > >         }
> > >         file://System.out.println ("modified SQL: " + sql);
> > > +
> > > +
> > > +
> > > +
> > > +       file://---- Added by Arsalan
> > > +       String query = sql;
> > > +
> > > +       file://2== uninitialised 1==true 0==false
> > > +       int openTick = 2;
> > > +       int openQuote = 2;
> > > +       char oneChar;
> > > +
> > > +       // query = query.toLowerCase();
> > > +
> > > +       if(query.indexOf(";") == -1) // no semi's at all
> > > +   {
> > >         return sql;
> > >     }
> > >
> > > +       for(int i=0; i<query.length(); i++)
> > > +   {
> > > +       oneChar = query.charAt(i);
> > > +
> > > +       if((oneChar == ';' && openTick == 0 && openQuote != 1) ||
> (oneChar == ';' && openQuote == 0 && openTick != 1))
> > > +   {
> > > +       throw new SQLException("Found an external SEMICOLON!! at i =
> "+i);
> > > +   }
> > > +
> > > +       if(oneChar == '\'') file://is a '
> > > +   {
> > > +
> > > +       System.out.println("in '");
> > > +       int j = i;
> > > +       if(++j < query.length())
> > > +   {
> > > +       if(query.charAt(j) == '\'') file://is an escape
> > > +   {
> > > +       i++; file://skip them
> > > +       continue;
> > > +   }
> > > +       else file://genuine tick!
> > > +   {
> > > +       if(openTick == 0)
> > > +   {
> > > +       openTick = 1;
> > > +   }
> > > +       else if(openTick == 1)
> > > +   {
> > > +       openTick = 0;
> > > +   }
> > > +       else if(openTick == 2) file://initialise it
> > > +   {
> > > +       openTick = 1;
> > > +   }
> > > +   }
> > > +   }
> > > +   }
> > > +
> > > +
> > > +       if(oneChar == '"') file://is a "
> > > +   {
> > > +       System.out.println("in \"");
> > > +       int j = i;
> > > +       if(++j < query.length())
> > > +   {
> > > +       if(query.charAt(j) == '"') file://is an escape
> > > +   {
> > > +       i++; file://skip them
> > > +       continue;
> > > +   }
> > > +       else file://genuine quote!
> > > +   {
> > > +       if(openQuote == 0)
> > > +
>
> > > +       openQuote = 1;
> > > +   }
> > > +       else if(openQuote == 1)
> > > +   {
> > > +       openQuote = 0;
> > > +   }
> > > +       else if(openQuote == 2) file://initialise it
> > > +   {
> > > +       openQuote = 1;
> > > +   }
> > > +   }
> > > +   }
> > > +   }
> > > +
> > > +
> > > +   }
> > > +
> > > +
> > > +
> > > +
> > > +       file://------Addition ends
> > > +
> > > +
> > > +
> > > +       return sql;
> > >       }
> > >
> > >
> > > ------------------------------------------------------------------------
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > >
> > > /bjm/diff
> > >
> > > Content-Type:
> > >
> > > text/plain
> > > Content-Encoding:
> > >
> > > 7bit
> > >
> > >
> > > ------------------------------------------------------------------------
> > > Part 1.3
> > >
> > > Content-Type:
> > >
> > > text/plain
> > > Content-Encoding:
> > >
> > > binary
> > >
> > >
> >
> >
>
>
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026