Handling apostrophes

[da...@bt.com]

Hi all,

	I'm developing an application which uses java servlets and JSPs and
a MySQL database running on Tomcat 4.0.

	I take user input, store it on the db, then display it again. As
soon as someone tried inputiing an apostrophe, it all fell over. It seems
that I have to encode and decode every single text field. Is this correct,
or is there a better way ?

Thanks

Dave

Re: Handling apostrophes

[David Treves <dw...@macam.ac.il>]

Hi there,

you should simply duplicate in every input string the apostrophe.

Meaning that if the input string is:

eee'eee

after manipulating it - BEFORE inserting it to the DB it will be:

eee''eee  (  ' twice, NOT A double quote)

in the DB it will appear as SINGLE apostrophe.


That will work!  :o)
David.

----- Original Message -----
From: <da...@bt.com>
To: <to...@jakarta.apache.org>
Sent: Monday, October 22, 2001 10:55 AM
Subject: Handling apostrophes


> Hi all,
>
> I'm developing an application which uses java servlets and JSPs and
> a MySQL database running on Tomcat 4.0.
>
> I take user input, store it on the db, then display it again. As
> soon as someone tried inputiing an apostrophe, it all fell over. It seems
> that I have to encode and decode every single text field. Is this correct,
> or is there a better way ?
>
> Thanks
>
> Dave
>
>

RE: Handling apostrophes

[Deacon Marcus <de...@wwtech.pl>]

Hi,
Try using prepared statements. They allow parameters, so basically you
define a query with parameters, then set parameter values, and jdbc takes
care of all char-quoting.

Greetings, deacon Marcus

> -----Original Message-----
> From: dave.prout@bt.com [mailto:dave.prout@bt.com]
> Sent: Monday, October 22, 2001 10:55 AM
> To: tomcat-user@jakarta.apache.org
> Subject: Handling apostrophes
>
>
> Hi all,
>
> 	I'm developing an application which uses java servlets and JSPs and
> a MySQL database running on Tomcat 4.0.
>
> 	I take user input, store it on the db, then display it again. As
> soon as someone tried inputiing an apostrophe, it all fell over. It seems
> that I have to encode and decode every single text field. Is this correct,
> or is there a better way ?
>
> Thanks
>
> Dave
>
>
>

Re: Handling apostrophes

[Richard Troy <rt...@ScienceTools.com>]

Yes, Dave,

Though this is off-topic, databases are my thing, so here's an answer:

Handling of qoutation marks and apostrophies are definete problem areas
with any database access, depending on how you formulate your queries.
If you embed your values to insert or update in Strings that make up an
sql statement, you are bound to have trouble. You can instead use 'set' or
'update' methods which handle the data "under the sheets" and can avoid
this problem. In such access, you'd use the question mark ('?') inside
your sql syntax in a prepared statement, or you'd identify your attributes
in your preceeding 'select' statement.

My company provides a Java-based API for our products and we need to pass
sql through the interface. Customers, of course, are want to do everything
sloppily, so, we wrote a few methods you might also want to write. First,
recognize that some database engines use "single quotes"  - apostrophies -
while others use "double quotes" - the real quote character - while still
others will take either, and whichever one starts a quoted string will be
the one to end it. So, we wrote methods to help. One looks for proper
quoting in the string before considering it valid sql - the method returns
a boolean... Another method takes the sql and builds it correctly given a
variable someone wants to include in their sql, and so on.

In short, never take user input and blindly construct an SQL query string
with it. ...You _will_ get bitten on the arse.

...Oh, as an aside, I don't quite understand what the tokenizer Tarek
wrote about is supposed to do for you. Maybe he has something there, but
it wasn't clear to me how it would help you.

Regards,
RT

-- 
Richard Troy, Chief Scientist
Science Tools Corporation
rtroy@ScienceTools.com, 510-567-9957, http://ScienceTools.com/

On Mon, 22 Oct 2001 dave.prout@bt.com wrote:

> Date: Mon, 22 Oct 2001 09:55:17 +0100
> From: dave.prout@bt.com
> Reply-To: tomcat-user@jakarta.apache.org
> To: tomcat-user@jakarta.apache.org
> Subject: Handling apostrophes
>
> Hi all,
>
> 	I'm developing an application which uses java servlets and JSPs and
> a MySQL database running on Tomcat 4.0.
>
> 	I take user input, store it on the db, then display it again. As
> soon as someone tried inputiing an apostrophe, it all fell over. It seems
> that I have to encode and decode every single text field. Is this correct,
> or is there a better way ?
>
> Thanks
>
> Dave
>
>
>