You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Martin Tilsted <ti...@daimi.au.dk> on 2008/03/07 19:00:35 UTC
What is the best way to escape input to SqlEnum.CUSTOM?
What is the best way to escape a (user input) value that is used as part
of a SqlEnum.CUSTOM query?
Is there a method that will escape strings based on the requirements of
the current open database connection?
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: What is the best way to escape input to SqlEnum.CUSTOM?
Posted by Martin Tilsted <ti...@daimi.au.dk>.
Aha thank you. I knew it was there someware I could just not find it.
Greg Monroe wrote:
> Yes, you're right. I missed a test up the line for
> Custom. So, the answer is to use the static method:
>
> SQLExpression.quoteAndEscapeText(String, DB)
>
> on the applicable parts of your custom criteria.
> This will return a String quoted for the specific
> DB type. E.g.:
>
> String likePart = "%"+someText+"%";
> DB db = Torque.getDB(TablePeer.DATABASE_NAME);
> likePart = SqlExpression.quoteAndEscapeString(
> likePart, db);
> String customPart = "myField like " + likePart;
>
>
>
>> -----Original Message-----
>> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
>> Sent: Friday, March 07, 2008 1:55 PM
>> To: Apache Torque Users List
>> Subject: Re: What is the best way to escape input to SqlEnum.CUSTOM?
>>
>> Greg Monroe wrote:
>>
>>> As long as the Criteria value object is a String, you don't
>>> have to worry. The code should be calling the following
>>> method:
>>>
>>> SqlExpression.quoteAndEscapeText(String rawText, DB db)
>>>
>>> This surrounds the value with the correct quotes and
>>> escapes stuff based on the type of DB.
>>>
>>>
>> But that can't be work for SqlEnum.CUSTOM because what if the query is
>> something like
>>
>> String customPart="myField like '%" + someText + "%'";
>> where someText is the user input.
>>
>> Doing a criteria.add("Table",customPart,SqlEnum.CUSTOM);
>> If it excape the entire query then the result will be wrong, because
>>
> it
>
>> will also escape the ' before the %, and that should not be escaped.
>>
>> Which is why I am pretty sure I need to escape the someText string
>> before i use it as part of a query with SqlEnum.CUSTOM.
>> (I know the query can be made without SqlEnum.CUSTOM it's just an
>> example).
>>
>>>> -----Original Message-----
>>>> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
>>>> Sent: Friday, March 07, 2008 1:01 PM
>>>> To: Apache Torque Users List
>>>> Subject: What is the best way to escape input to SqlEnum.CUSTOM?
>>>>
>>>> What is the best way to escape a (user input) value that is used as
>>>> part
>>>> of a SqlEnum.CUSTOM query?
>>>>
>>>> Is there a method that will escape strings based on the
>>>>
> requirements
>
>>> of
>>>
>>>
>>>> the current open database connection?
>>>>
>>>> Martin
>>>>
>>>>
>
>
RE: What is the best way to escape input to SqlEnum.CUSTOM?
Posted by Greg Monroe <Gr...@DukeCE.com>.
Yes, you're right. I missed a test up the line for
Custom. So, the answer is to use the static method:
SQLExpression.quoteAndEscapeText(String, DB)
on the applicable parts of your custom criteria.
This will return a String quoted for the specific
DB type. E.g.:
String likePart = "%"+someText+"%";
DB db = Torque.getDB(TablePeer.DATABASE_NAME);
likePart = SqlExpression.quoteAndEscapeString(
likePart, db);
String customPart = "myField like " + likePart;
> -----Original Message-----
> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
> Sent: Friday, March 07, 2008 1:55 PM
> To: Apache Torque Users List
> Subject: Re: What is the best way to escape input to SqlEnum.CUSTOM?
>
> Greg Monroe wrote:
> > As long as the Criteria value object is a String, you don't
> > have to worry. The code should be calling the following
> > method:
> >
> > SqlExpression.quoteAndEscapeText(String rawText, DB db)
> >
> > This surrounds the value with the correct quotes and
> > escapes stuff based on the type of DB.
> >
> But that can't be work for SqlEnum.CUSTOM because what if the query is
> something like
>
> String customPart="myField like '%" + someText + "%'";
> where someText is the user input.
>
> Doing a criteria.add("Table",customPart,SqlEnum.CUSTOM);
> If it excape the entire query then the result will be wrong, because
it
> will also escape the ' before the %, and that should not be escaped.
>
> Which is why I am pretty sure I need to escape the someText string
> before i use it as part of a query with SqlEnum.CUSTOM.
> (I know the query can be made without SqlEnum.CUSTOM it's just an
> example).
> >
> >> -----Original Message-----
> >> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
> >> Sent: Friday, March 07, 2008 1:01 PM
> >> To: Apache Torque Users List
> >> Subject: What is the best way to escape input to SqlEnum.CUSTOM?
> >>
> >> What is the best way to escape a (user input) value that is used as
> >> part
> >> of a SqlEnum.CUSTOM query?
> >>
> >> Is there a method that will escape strings based on the
requirements
> >>
> > of
> >
> >> the current open database connection?
> >>
> >> Martin
> >>
> >>
--------------------------------------------------------------------
> -
> >> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> >> For additional commands, e-mail: torque-user-help@db.apache.org
> >>
> >
> > DukeCE Privacy Statement:
> > Please be advised that this e-mail and any files transmitted with
> > it are confidential communication or may otherwise be privileged or
> > confidential and are intended solely for the individual or entity
> > to whom they are addressed. If you are not the intended recipient
> > you may not rely on the contents of this email or any attachments,
> > and we ask that you please not read, copy or retransmit this
> > communication, but reply to the sender and destroy the email, its
> > contents, and all copies thereof immediately. Any unauthorized
> > dissemination, distribution or copying of this communication is
> > strictly prohibited.
> >
> >
---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> >
> >
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: What is the best way to escape input to SqlEnum.CUSTOM?
Posted by Martin Tilsted <ti...@daimi.au.dk>.
Greg Monroe wrote:
> As long as the Criteria value object is a String, you don't
> have to worry. The code should be calling the following
> method:
>
> SqlExpression.quoteAndEscapeText(String rawText, DB db)
>
> This surrounds the value with the correct quotes and
> escapes stuff based on the type of DB.
>
But that can't be work for SqlEnum.CUSTOM because what if the query is
something like
String customPart="myField like '%" + someText + "%'";
where someText is the user input.
Doing a criteria.add("Table",customPart,SqlEnum.CUSTOM);
If it excape the entire query then the result will be wrong, because it
will also escape the ' before the %, and that should not be escaped.
Which is why I am pretty sure I need to escape the someText string
before i use it as part of a query with SqlEnum.CUSTOM.
(I know the query can be made without SqlEnum.CUSTOM it's just an example).
>
>> -----Original Message-----
>> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
>> Sent: Friday, March 07, 2008 1:01 PM
>> To: Apache Torque Users List
>> Subject: What is the best way to escape input to SqlEnum.CUSTOM?
>>
>> What is the best way to escape a (user input) value that is used as
>> part
>> of a SqlEnum.CUSTOM query?
>>
>> Is there a method that will escape strings based on the requirements
>>
> of
>
>> the current open database connection?
>>
>> Martin
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: torque-user-help@db.apache.org
>>
>
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
RE: What is the best way to escape input to SqlEnum.CUSTOM?
Posted by Greg Monroe <Gr...@DukeCE.com>.
As long as the Criteria value object is a String, you don't
have to worry. The code should be calling the following
method:
SqlExpression.quoteAndEscapeText(String rawText, DB db)
This surrounds the value with the correct quotes and
escapes stuff based on the type of DB.
> -----Original Message-----
> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
> Sent: Friday, March 07, 2008 1:01 PM
> To: Apache Torque Users List
> Subject: What is the best way to escape input to SqlEnum.CUSTOM?
>
> What is the best way to escape a (user input) value that is used as
> part
> of a SqlEnum.CUSTOM query?
>
> Is there a method that will escape strings based on the requirements
of
> the current open database connection?
>
> Martin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org