You are viewing a plain text version of this content. The canonical link for it is here.
Posted to slide-dev@jakarta.apache.org by Jean-Philippe Courson <co...@noos.fr> on 2002/08/19 17:09:22 UTC
J2EEStore and specials characters
Hi,
A lot of SQL requests into J2EEStore are made using java.sql.Statement
and ' into them
as string delimiter.
Using URIs with special characters can easily lead to SQL errors if they
are containing
for example ' character.
Would not it be a better idea to use java.sql.PreparedStatement instead
to avoid all these
problems ?
Any opinion on this ?
Regards
Jp
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Colin Britton <cb...@metatomix.com>.
We have a util class to do sql string encoding, we would be happy to
contribute (ashok can add it to the stores).
One question: Is the cost of making prepared statements really any worse
than running every string through this?
public static String getSQLString(String theString)
{
String temp = null;
String temp1 = "";
// Check for null string
if (theString == null)
{
return temp;
}
// Change single quotes to two single quotes
int index = 0;
int nextIndex = 0;
temp = theString.trim();
if (temp.length() > 0)
{
if (temp.indexOf(39) != -1)
{
while (nextIndex != -1)
{
temp1 = temp;
index = temp.indexOf(39, nextIndex);
if (index != -1)
{
temp = temp1.substring(0, index);
temp += "\'";
temp += temp1.substring(index, temp1.length());
}
nextIndex = temp.indexOf(39, nextIndex + 2);
}
}
}
String retVal = "'" + temp + "'";
return retVal;
}
rgds
CB
----- Original Message -----
From: "Christopher Lenz" <cm...@gmx.de>
To: "Slide Developers Mailing List" <sl...@jakarta.apache.org>
Sent: Monday, August 19, 2002 11:50 AM
Subject: Re: J2EEStore and specials characters
> Hi Jean-Philippe,
>
> as some have reported, using PreparedStatements in the way we were doing
before
> - i.e. creating them for each execution - has serious performance
drawbacks. I
> haven't verified that, but it does make a lot of sense.
>
> For the problem you're describing, wouldn't it be enough to escape the '
> characters inside Strings ?
>
> Am 19.08.2002 17:09:22, schrieb Jean-Philippe Courson <co...@noos.fr>:
> >Hi,
> >
> >A lot of SQL requests into J2EEStore are made using java.sql.Statement
> >and ' into them
> >as string delimiter.
> >
> >Using URIs with special characters can easily lead to SQL errors if they
> >are containing
> >for example ' character.
> >
> >Would not it be a better idea to use java.sql.PreparedStatement instead
> >to avoid all these
> >problems ?
> >
> >Any opinion on this ?
>
> --
> Christopher Lenz
> _______________________________________________
> /=/ cmlenz at gmx.de
>
>
>
>
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Jean-Philippe Courson <co...@noos.fr>.
Hi Christopher,
>Hi Jean-Philippe,
>
>Am 23.08.2002 13:42:34, schrieb Jean-Philippe Courson <co...@noos.fr>:
>
>
>>Christopher Lenz wrote:
>>
>>
>>>Am 23.08.2002 11:23:39, schrieb Jean-Philippe Courson <co...@noos.fr>:
>>>
>>>
>>>>The problem seems only to consern uris (set/getUri methods) and
>>>>properties (have not look at the code).
>>>>So escaping uris and properties names and values should be enough (' and
>>>>" characters for example with colin's code)
>>>>
>>>>
>>>I've added a very (very very ;o)) simple escaping routine that only escapes
>>>the ' character. I don't think we need to escape the " character, or do we?
>>>(and if yes why?)
>>>
>>>
>>>
>>It think that (at least) " and \ characters have to be encoded too.
>>
>>
>
>Okay \ makes sense but I don't know about ".
>
>
>
>>Problems could occur 1°) with ressources names 2°) with properties names
>>or values.
>>
>>1°) For ressources names :
>>Effectivelly Windows does not let you use " and \ characters into files
>>or directories names, but linux for example does :-(
>>So importing such ressources to Slide could lead to problems.
>>
>>2°) For properties names and values : I don't know if webDAV spec are
>>advising something about this but displayname property
>>itself could leads to the same sort of problems.
>>
>>
>
>But those are problems that should be adressed above the store layer IMO (in the
>WebDAV layer, probably). The encoding in the stores using JDBC just needs to
>make sure the data gets properly stored in the database. So a string containing
>a ' character would lead to an syntactically incorrect SQL statement. A string
>containing a \ would result in the character following the \ being dropped. If
>those two cases are remedied the store has done its job (again, IMO).
>
>
>
I agree with you.
Those problems have to be processed by above layers
Jp
>--
>Christopher Lenz
>_______________________________________________
> /=/ cmlenz at gmx.de
>
>
>
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Christopher Lenz <cm...@gmx.de>.
Hi Jean-Philippe,
Am 23.08.2002 13:42:34, schrieb Jean-Philippe Courson <co...@noos.fr>:
>Christopher Lenz wrote:
>>Am 23.08.2002 11:23:39, schrieb Jean-Philippe Courson <co...@noos.fr>:
>>>The problem seems only to consern uris (set/getUri methods) and
>>>properties (have not look at the code).
>>>So escaping uris and properties names and values should be enough (' and
>>>" characters for example with colin's code)
>>
>>I've added a very (very very ;o)) simple escaping routine that only escapes
>>the ' character. I don't think we need to escape the " character, or do we?
>>(and if yes why?)
>>
>It think that (at least) " and \ characters have to be encoded too.
Okay \ makes sense but I don't know about ".
>Problems could occur 1°) with ressources names 2°) with properties names
>or values.
>
>1°) For ressources names :
>Effectivelly Windows does not let you use " and \ characters into files
>or directories names, but linux for example does :-(
>So importing such ressources to Slide could lead to problems.
>
>2°) For properties names and values : I don't know if webDAV spec are
>advising something about this but displayname property
>itself could leads to the same sort of problems.
But those are problems that should be adressed above the store layer IMO (in the
WebDAV layer, probably). The encoding in the stores using JDBC just needs to
make sure the data gets properly stored in the database. So a string containing
a ' character would lead to an syntactically incorrect SQL statement. A string
containing a \ would result in the character following the \ being dropped. If
those two cases are remedied the store has done its job (again, IMO).
--
Christopher Lenz
_______________________________________________
/=/ cmlenz at gmx.de
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Jean-Philippe Courson <co...@noos.fr>.
Christopher Lenz wrote:
>Hi Jean-Philippe,
>
>Am 23.08.2002 11:23:39, schrieb Jean-Philippe Courson <co...@noos.fr>:
>
>
>>The problem seems only to consern uris (set/getUri methods) and
>>properties (have not look at the code).
>>So escaping uris and properties names and values should be enough (' and
>>" characters for example with colin's code)
>>
>>
>
>I've added a very (very very ;o)) simple escaping routine that only escapes the
>' character. I don't think we need to escape the " character, or do we? (and if
>yes why?)
>
It think that (at least) " and \ characters have to be encoded too.
Problems could occur 1°) with ressources names 2°) with properties names
or values.
1°) For ressources names :
Effectivelly Windows does not let you use " and \ characters into files
or directories names, but linux for example does :-(
So importing such ressources to Slide could lead to problems.
2°) For properties names and values : I don't know if webDAV spec are
advising something about this but displayname property
itself could leads to the same sort of problems.
Jp
>
>The routine is applied to property names and URIs only (property values are set
>in a prepared statement anyway). As both of those are also cached by the
>StandardRDBMSAdapter, I think the String-escaping should be a good solution also
>from the performance perspective.
>
>--
>Christopher Lenz
>_______________________________________________
> /=/ cmlenz at gmx.de
>
>
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Christopher Lenz <cm...@gmx.de>.
Hi Jean-Philippe,
Am 23.08.2002 11:23:39, schrieb Jean-Philippe Courson <co...@noos.fr>:
>The problem seems only to consern uris (set/getUri methods) and
>properties (have not look at the code).
>So escaping uris and properties names and values should be enough (' and
>" characters for example with colin's code)
I've added a very (very very ;o)) simple escaping routine that only escapes the
' character. I don't think we need to escape the " character, or do we? (and if
yes why?)
The routine is applied to property names and URIs only (property values are set
in a prepared statement anyway). As both of those are also cached by the
StandardRDBMSAdapter, I think the String-escaping should be a good solution also
from the performance perspective.
--
Christopher Lenz
_______________________________________________
/=/ cmlenz at gmx.de
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Jean-Philippe Courson <co...@noos.fr>.
Christopher Lenz wrote:
>Hi Jean-Philippe,
>
>as some have reported, using PreparedStatements in the way we were doing before
>- i.e. creating them for each execution - has serious performance drawbacks. I
>haven't verified that, but it does make a lot of sense.
>
>For the problem you're describing, wouldn't it be enough to escape the '
>characters inside Strings ?
>
>
Yes it should be enough, but it has to be done into the store.
Using webdav servlet, people can put to server files containing special
characters and for now this causes an SQLException.
The problem seems only to consern uris (set/getUri methods) and
properties (have not look at the code).
So escaping uris and properties names and values should be enough (' and
" characters for example with colin's code)
Thank you all
Jp
>Am 19.08.2002 17:09:22, schrieb Jean-Philippe Courson <co...@noos.fr>:
>
>
>>Hi,
>>
>>A lot of SQL requests into J2EEStore are made using java.sql.Statement
>>and ' into them
>>as string delimiter.
>>
>>Using URIs with special characters can easily lead to SQL errors if they
>>are containing
>>for example ' character.
>>
>>Would not it be a better idea to use java.sql.PreparedStatement instead
>>to avoid all these
>>problems ?
>>
>>Any opinion on this ?
>>
>>
>
>--
>Christopher Lenz
>_______________________________________________
> /=/ cmlenz at gmx.de
>
>
>
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: J2EEStore and specials characters
Posted by Christopher Lenz <cm...@gmx.de>.
Hi Jean-Philippe,
as some have reported, using PreparedStatements in the way we were doing before
- i.e. creating them for each execution - has serious performance drawbacks. I
haven't verified that, but it does make a lot of sense.
For the problem you're describing, wouldn't it be enough to escape the '
characters inside Strings ?
Am 19.08.2002 17:09:22, schrieb Jean-Philippe Courson <co...@noos.fr>:
>Hi,
>
>A lot of SQL requests into J2EEStore are made using java.sql.Statement
>and ' into them
>as string delimiter.
>
>Using URIs with special characters can easily lead to SQL errors if they
>are containing
>for example ' character.
>
>Would not it be a better idea to use java.sql.PreparedStatement instead
>to avoid all these
>problems ?
>
>Any opinion on this ?
--
Christopher Lenz
_______________________________________________
/=/ cmlenz at gmx.de
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>