Re: Patch for Statement Escape Processing problems

Hi all,

Wonder if anyone had a look at this patch yet? I've been using my locally
patched version for a while now and it works fine. Again just to make it
clear what it does...

- It fixes an Exception which will be thrown with the following insert.

insert into bbbb values ('xxxx{d');

- The current statement implementation also corrupts string data with '{d'
characters by removing them. This patch fixes this.

- The patch also adds support for {t and {ts escapes which are covered
in section 11.3 of the specification. Does anyone on the list use
these types of escapes???

Right now I'm mostly after fixing the corruption of strings as a lot of
my data contains the "{d" character combination and its quite annoying.

For those of you who don't know, the escapeSQL() method is called
everytime you execute a Statement or PreparedStatement if setEscapeProcessing
is set true (which is the default).

Tom.

On Mon, Sep 10, 2001 at 05:27:17PM +0900, Thomas O'Dowd wrote:
> Hi all,
>
> I'm attaching a patch which fixes the corruption in strings caused
> by escape processing in the SQL statement. I've tested this for a
> while now and it appears to work well. Previously string data
> with {d was getting corrupt as the {d was being stripped regardless
> of whether it was an escape code or not.
>
> I also added checking for time and timestamp escape processing strings
> as per 11.3 in the specification. The patch is against the latest
> CVS.
>
> Cheers,
>
> Tom.

> Index: org/postgresql/Statement.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Statement.java,v
> retrieving revision 1.2
> diff -c -r1.2 Statement.java
> *** org/postgresql/Statement.java 2001/08/10 14:42:07 1.2
> --- org/postgresql/Statement.java 2001/09/10 08:16:48
> ***************
> *** 39,44 ****
> --- 39,49 ----
>
> protected boolean escapeProcessing = true;
>
> + // Static variables for parsing SQL when escapeProcessing is true.
> + private static final short IN_SQLCODE = 0;
> + private static final short IN_STRING = 1;
> + private static final short BACKSLASH =2;
> + private static final short ESC_TIMEDATE = 3;
>
> public Statement() {
> }
> ***************
> *** 226,251 ****
> }
>
> /**
> ! * This is an attempt to implement SQL Escape clauses
> ! */
> ! protected static String escapeSQL(String sql) {
> ! // If we find a "{d", assume we have a date escape.
> ! //
> ! // Since the date escape syntax is very close to the
> ! // native Postgres date format, we just remove the escape
> ! // delimiters.
> ! //
> ! // This implementation could use some optimization, but it has
> ! // worked in practice for two years of solid use.
> ! int index = sql.indexOf("{d");
> ! while (index != -1) {
> ! StringBuffer buf = new StringBuffer(sql);
> ! buf.setCharAt(index, ' ');
> ! buf.setCharAt(index + 1, ' ');
> ! buf.setCharAt(sql.indexOf('}', index), ' ');
> ! sql = new String(buf);
> ! index = sql.indexOf("{d");
> ! }
> ! return sql;
> }
> }
> --- 231,306 ----
> }
>
> /**
> ! * Filter the SQL string of Java SQL Escape clauses.
> ! *
> ! * Currently implemented Escape clauses are those mentioned in 11.3
> ! * in the specification. Basically we look through the sql string for
> ! * {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
> ! * them, we just strip the escape part leaving only the xxx part.
> ! * So, something like "select * from x where d={d '2001-10-09'}" would
> ! * return "select * from x where d= '2001-10-09'".
> ! */
> ! protected static String escapeSQL(String sql)
> ! {
> ! // Since escape codes can only appear in SQL CODE, we keep track
> ! // of if we enter a string or not.
> ! StringBuffer newsql = new StringBuffer();
> ! short state = IN_SQLCODE;
> !
> ! int i = -1;
> ! int len = sql.length();
> ! while(++i < len)
> ! {
> ! char c = sql.charAt(i);
> ! switch(state)
> ! {
> ! case IN_SQLCODE:
> ! if(c == '\'') // start of a string?
> ! state = IN_STRING;
> ! else if(c == '{') // start of an escape code?
> ! if(i+1 < len)
> ! {
> ! char next = sql.charAt(i+1);
> ! if(next == 'd')
> ! {
> ! state = ESC_TIMEDATE;
> ! i++;
> ! break;
> ! }
> ! else if(next == 't')
> ! {
> ! state = ESC_TIMEDATE;
> ! i += (i+2 < len && sql.charAt(i+2) == 's') ? 2 : 1;
> ! break;
> ! }
> ! }
> ! newsql.append(c);
> ! break;
> !
> ! case IN_STRING:
> ! if(c == '\'') // end of string?
> ! state = IN_SQLCODE;
> ! else if(c == '\\') // a backslash?
> ! state = BACKSLASH;
> !
> ! newsql.append(c);
> ! break;
> !
> ! case BACKSLASH:
> ! state = IN_STRING;
> !
> ! newsql.append(c);
> ! break;
> !
> ! case ESC_TIMEDATE:
> ! if(c == '}')
> ! state = IN_SQLCODE; // end of escape code.
> ! else
> ! newsql.append(c);
> ! break;
> ! } // end switch
> ! }
> !
> ! return newsql.toString();
> }
> }

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom(at)nooper(dot)com - Testing - http://nooper.co.jp/labs