Re: [INTERFACES] New code for JDBC driver

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(at)xythos(dot)com>
> To: PostgreSQL jdbc list <pgsql-jdbc(at)postgresql(dot)org>
> Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>; Arsalan Zaidi
> <azaidi(at)directi(dot)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(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026