Thread: I'm reading the source now...
Hello,
Trying to figure out how to integrate
Statement.setEscapeProcessing(true)
into a JSP it came upon me that to make the JSP RDBMS independant this
code should be in the driver. So I downloaded the source and came up
with
public boolean execute(String sql) throws SQLException
{
if (escapeProcessing)
sql = escapeSQL(sql);
in statement. I'm using preparedStatement which if one reads Suns doc
correctly escape characters "automagically".
Can I just use escapeSQL in my query???
Cheers
Tony Grant
--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html
PS just because this escaping single quote thing has been driving me nuts for 2 months or more and pushed me into learning more about Java does it mean that I am capable of reading all the source and then proposing a patch... Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Tony, What would be helpful here is a test case which exhibits the error; otherwise it's kind of like looking for a needle in a haystack. Dave On Tue, 2002-04-30 at 13:02, tony wrote: > PS > > just because this escaping single quote thing has been driving me nuts > for 2 months or more and pushed me into learning more about Java does it > mean that I am capable of reading all the source and then proposing a > patch... > > Cheers > > Tony > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia UltraDev with PostgreSQL > http://www.animaproductions.com/ultra.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
On Tue, 2002-04-30 at 20:53, Dave Cramer wrote: > What would be helpful here is a test case which exhibits the error; > otherwise it's kind of like looking for a needle in a haystack. http://mail.animaproductions.com/f3c look for N' in the first search box. It should bring up a famous african director. When I wrote the original version of the Ultradev web page I had built a flat text perl "database" app. for a client. I then rewrote everything in JSP. At that time when I did a select from the database I could use single quotes in queries with no code other than the code generated by Ultradev. I remember that distinctly because my client asked me for accents and single quotes in queries. I used to_ascii() in the select statements and it just worked. All was well until I noticed that this was no longer the case after I upgraded my server after my hardware adventures of last summer... I moved to postgres 7.0 and Tomcat 3.2. §§§§§§§§§§§§§§§§§§§ All is well. I have now written the methods into the pages. I'll stop bugging everyone on this thing. At least it got me real deep into understanding Java. But from my interpretation of the docs at Sun escapeSQL is something that one finds in preparedStatement in the JDBC driver. I am probably wrong. Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Tony, Actually, it errors out right now. Do you know what the code does with it? ie. what is the resulting query? Dave On Tue, 2002-04-30 at 15:29, tony wrote: > On Tue, 2002-04-30 at 20:53, Dave Cramer wrote: > > > What would be helpful here is a test case which exhibits the error; > > otherwise it's kind of like looking for a needle in a haystack. > > http://mail.animaproductions.com/f3c > > look for N' in the first search box. It should bring up a famous > african director. > > When I wrote the original version of the Ultradev web page I had built a > flat text perl "database" app. for a client. I then rewrote everything > in JSP. At that time when I did a select from the database I could use > single quotes in queries with no code other than the code generated by > Ultradev. > > I remember that distinctly because my client asked me for accents and > single quotes in queries. I used to_ascii() in the select statements and > it just worked. > > All was well until I noticed that this was no longer the case after I > upgraded my server after my hardware adventures of last summer... > > I moved to postgres 7.0 and Tomcat 3.2. > > §§§§§§§§§§§§§§§§§§§ > All is well. > > I have now written the methods into the pages. I'll stop bugging > everyone on this thing. At least it got me real deep into understanding > Java. But from my interpretation of the docs at Sun escapeSQL is > something that one finds in preparedStatement in the JDBC driver. I am > probably wrong. > > Cheers > > Tony Grant > > > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia UltraDev with PostgreSQL > http://www.animaproductions.com/ultra.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Tony, Actually reading the spec myself, there's nothing the driver can do with N' as a query if it is just through a Statement.executeQuery, if however it is through a PreparedStatment.setString, then it can be dealt with I think you need to catch the input before hand and escape it Dave On Tue, 2002-04-30 at 15:29, tony wrote: > On Tue, 2002-04-30 at 20:53, Dave Cramer wrote: > > > What would be helpful here is a test case which exhibits the error; > > otherwise it's kind of like looking for a needle in a haystack. > > http://mail.animaproductions.com/f3c > > look for N' in the first search box. It should bring up a famous > african director. > > When I wrote the original version of the Ultradev web page I had built a > flat text perl "database" app. for a client. I then rewrote everything > in JSP. At that time when I did a select from the database I could use > single quotes in queries with no code other than the code generated by > Ultradev. > > I remember that distinctly because my client asked me for accents and > single quotes in queries. I used to_ascii() in the select statements and > it just worked. > > All was well until I noticed that this was no longer the case after I > upgraded my server after my hardware adventures of last summer... > > I moved to postgres 7.0 and Tomcat 3.2. > > §§§§§§§§§§§§§§§§§§§ > All is well. > > I have now written the methods into the pages. I'll stop bugging > everyone on this thing. At least it got me real deep into understanding > Java. But from my interpretation of the docs at Sun escapeSQL is > something that one finds in preparedStatement in the JDBC driver. I am > probably wrong. > > Cheers > > Tony Grant > > > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia UltraDev with PostgreSQL > http://www.animaproductions.com/ultra.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Tony,
Wonder if you could send the actual code around the query as this is
a rather curious error. Are you actually using the setString() method
of the PreparedStatement to set up the string? If so what does your
prepared statement template look like? It should be something like...
"SELECT name FROM table WHERE name ilike %"
And then the string that you set in the query should be setup using
something like...
ps.setString(1, varname + "%"); // maybe add the percent for ilike?
// don't add it in the PS template.
If you print varname, before setting it, it should be just "N'"...
Try printing the prepared statement also before executing it. You can
just us System.out.println(ps.toString()); The result should be something
like this...
"SELECT name FROM table WHERE name ilike 'N\'%'"
Tom.
On Tue, Apr 30, 2002 at 04:21:51PM -0400, Dave Cramer wrote:
> Tony,
>
> Actually reading the spec myself, there's nothing the driver can do with
> N' as a query if it is just through a Statement.executeQuery,
> if however it is through a PreparedStatment.setString, then it can be
> dealt with
>
> I think you need to catch the input before hand and escape it
>
> Dave
> On Tue, 2002-04-30 at 15:29, tony wrote:
> > On Tue, 2002-04-30 at 20:53, Dave Cramer wrote:
> >
> > > What would be helpful here is a test case which exhibits the error;
> > > otherwise it's kind of like looking for a needle in a haystack.
> >
> > http://mail.animaproductions.com/f3c
> >
> > look for N' in the first search box. It should bring up a famous
> > african director.
> >
> > When I wrote the original version of the Ultradev web page I had built a
> > flat text perl "database" app. for a client. I then rewrote everything
> > in JSP. At that time when I did a select from the database I could use
> > single quotes in queries with no code other than the code generated by
> > Ultradev.
> >
> > I remember that distinctly because my client asked me for accents and
> > single quotes in queries. I used to_ascii() in the select statements and
> > it just worked.
> >
> > All was well until I noticed that this was no longer the case after I
> > upgraded my server after my hardware adventures of last summer...
> >
> > I moved to postgres 7.0 and Tomcat 3.2.
> >
> > §§§§§§§§§§§§§§§§§§§
> > All is well.
> >
> > I have now written the methods into the pages. I'll stop bugging
> > everyone on this thing. At least it got me real deep into understanding
> > Java. But from my interpretation of the docs at Sun escapeSQL is
> > something that one finds in preparedStatement in the JDBC driver. I am
> > probably wrong.
> >
> > Cheers
> >
> > Tony Grant
> >
> >
> >
> > --
> > RedHat Linux on Sony Vaio C1XD/S
> > http://www.animaproductions.com/linux2.html
> > Macromedia UltraDev with PostgreSQL
> > http://www.animaproductions.com/ultra.html
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
Thomas, does this actually work? I would have thought the select should have been "SELECT name FROM table WHERE name ilike ?" Dave On Tue, 2002-04-30 at 19:25, Thomas O'Dowd wrote: > Tony, > > Wonder if you could send the actual code around the query as this is > a rather curious error. Are you actually using the setString() method > of the PreparedStatement to set up the string? If so what does your > prepared statement template look like? It should be something like... > > "SELECT name FROM table WHERE name ilike %" > > And then the string that you set in the query should be setup using > something like... > > ps.setString(1, varname + "%"); // maybe add the percent for ilike? > // don't add it in the PS template. > > If you print varname, before setting it, it should be just "N'"... > > Try printing the prepared statement also before executing it. You can > just us System.out.println(ps.toString()); The result should be something > like this... > > "SELECT name FROM table WHERE name ilike 'N\'%'" > > Tom. > > On Tue, Apr 30, 2002 at 04:21:51PM -0400, Dave Cramer wrote: > > Tony, > > > > Actually reading the spec myself, there's nothing the driver can do with > > N' as a query if it is just through a Statement.executeQuery, > > if however it is through a PreparedStatment.setString, then it can be > > dealt with > > > > I think you need to catch the input before hand and escape it > > > > Dave > > On Tue, 2002-04-30 at 15:29, tony wrote: > > > On Tue, 2002-04-30 at 20:53, Dave Cramer wrote: > > > > > > > What would be helpful here is a test case which exhibits the error; > > > > otherwise it's kind of like looking for a needle in a haystack. > > > > > > http://mail.animaproductions.com/f3c > > > > > > look for N' in the first search box. It should bring up a famous > > > african director. > > > > > > When I wrote the original version of the Ultradev web page I had built a > > > flat text perl "database" app. for a client. I then rewrote everything > > > in JSP. At that time when I did a select from the database I could use > > > single quotes in queries with no code other than the code generated by > > > Ultradev. > > > > > > I remember that distinctly because my client asked me for accents and > > > single quotes in queries. I used to_ascii() in the select statements and > > > it just worked. > > > > > > All was well until I noticed that this was no longer the case after I > > > upgraded my server after my hardware adventures of last summer... > > > > > > I moved to postgres 7.0 and Tomcat 3.2. > > > > > > §§§§§§§§§§§§§§§§§§§ > > > All is well. > > > > > > I have now written the methods into the pages. I'll stop bugging > > > everyone on this thing. At least it got me real deep into understanding > > > Java. But from my interpretation of the docs at Sun escapeSQL is > > > something that one finds in preparedStatement in the JDBC driver. I am > > > probably wrong. > > > > > > Cheers > > > > > > Tony Grant > > > > > > > > > > > > -- > > > RedHat Linux on Sony Vaio C1XD/S > > > http://www.animaproductions.com/linux2.html > > > Macromedia UltraDev with PostgreSQL > > > http://www.animaproductions.com/ultra.html > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- > Thomas O'Dowd. - Nooping - http://nooper.com > tom@nooper.com - Testing - http://nooper.co.jp/labs > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Opps... I meant '?' honest I did, serves me right for not re-reading my mail! Good catch Dave, and Tony if you've been trying to use the '%' as a result of my sillyness, I appologise!!! But its the same principle as I mentioned before... Tom. On Tue, Apr 30, 2002 at 10:27:00PM -0400, Dave Cramer wrote: > Thomas, > > does this actually work? > > I would have thought the select should have been > > "SELECT name FROM table WHERE name ilike ?" > > Dave -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
Tony,
setEscapeProcessing() and escapeSQL() do not do what you think they do.
They have nothing to do with quotes and such. Their purpose is as
defined in the jdbc spec to deal with handling special Escape commands
like the following: {d '2001-10-09'} . This allows one to put a date
literal into a sql statement without knowing the specific date format a
particular RDBMS might be expecting. These special escapes are designed
to help jdbc code be more database independent. The postgres jdbc
driver does not support all of the special Escapes defined by the jdbc
spec, but according to the source code it does support:
/*
* 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'".
*/
thanks,
--Barry
tony wrote:
> Hello,
>
> Trying to figure out how to integrate
> Statement.setEscapeProcessing(true)
>
> into a JSP it came upon me that to make the JSP RDBMS independant this
> code should be in the driver. So I downloaded the source and came up
> with
>
> public boolean execute(String sql) throws SQLException
> {
> if (escapeProcessing)
> sql = escapeSQL(sql);
>
> in statement. I'm using preparedStatement which if one reads Suns doc
> correctly escape characters "automagically".
>
> Can I just use escapeSQL in my query???
>
> Cheers
>
> Tony Grant
>
On Wed, 2002-05-01 at 01:25, Thomas O'Dowd wrote:
> "SELECT name FROM table WHERE name ilike %"
>
> And then the string that you set in the query should be setup using
> something like...
>
> ps.setString(1, varname + "%"); // maybe add the percent for ilike?
> // don't add it in the PS template.
>
You win!
The setString() code is missing!!!
I won't try and figure what has changed in my way of building the JSPs.
I will try writing a custom taglib with the escape function in it.
Thanks to all for the information on the JDBC driver.
RESULT:
- I will look at Deamweaver MX and if it isn't up to my expectations
(which have risen over recent weeks) I'll move to NetBeans.
- I will keep learning Java
Thanks to all for your time
Cheers
Tony
--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html