The problem is with the SQL statement. It uses ' to bracket strings, so if
your string contains ' it assumes it's the end of the string and continues
on it's way. The only way to get around this is to escape the ' characters.
In ANSI SQL you simply use '' (two single quotes) to cause a single ' to be
entered in the string. So, consider the following example:
INSERT INTO users (firstname, lastname, notes) VALUES ('John', 'Doe', 'This
isn't fair');
It would fail because the ' in 'This isn't fair' causes the SQL statement
parser to presume the string to be 'This isn' and then expects some more
valid SQL commands. However, the following statement would perform the
correct insertion
INSERT INTO users (firstname, lastname, notes) VALUES ('John', 'Doe', 'This
isn''t fair');
where 'This isn''t fair' doesn't contain a " (double quote) but rather
escapes the ' (single quote) problem by using two single quotes.
This might seem a bit confusing, but it's the only way to do it and conform
to ANSI SQL.
The other option you have is to use Java's escape() function which will
convert all non-alphanumeric characters into a two digit escape sequence
thus:
'The cat sat on the mat'
would become
'The%20cat%20sat%20on%20the%20mat'
You could then use that in your SQL statements, but remember when you
retreive the values from your database you must unescape() the stored
strings. I've found this method to be unsatisfactory as it means you can't
have text fields in your database of a limited length. If you had a text
field of say, 50 characters, someone entering data could type 50 characters,
which would be fine, but then if you use escape() on it, the string could
balloon up to 150 characters.
The best way I've found is simply to write a small function which I use on
all strings being used in SQL statements. This is good practice anyway as
it means you can modify that one function to do other checking on strings
being stored in your database (e.g. stripping control characters from them,
or removing HTML from them etc).
Hope this helps!
-----Original Message-----
From: Jose Luis Diaz [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ]
Sent: Thursday, November 18, 1999 3:04 AM
To: [EMAIL PROTECTED]
Subject: OFF-TOPIC: Problems with Form in JSP
I have a problem when the forms in JSP, really the problem is with SQL, when
I try to INSERT the data of a form with an SQL statement in a JSP, if the
data contains " or ' the result is incorrect.
I could parse ALL the insert I have done but I find it tedious, Is there any
solution to the INSERT problem with " or ' in Java, JSP or SQL.
Thanks.
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
