Two Laptop Bag
The moose likes JDBC and Relational Databases and the fly likes Inserting Apostrophes Into MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Inserting Apostrophes Into MySQL" Watch "Inserting Apostrophes Into MySQL" New topic

Inserting Apostrophes Into MySQL

Ma New

Joined: Sep 22, 2008
Posts: 12
I am relatively new to JSP and am trying to make a form to insert some articles my friends and I are writing. The problem is when I try to insert an apostrophe into my form it causes an error in my JSP. I have read some things on escape characters and such but I am a little confused. I have also tried using tinyMCE so I can keep the punctation for my articles but that seems to present problems because it is inserting special characters to form the HTML. I tried using an escapeXML string for my input but when I display the page all the characters are escaped. What should I do? Is there a setting in MySQL or is there some kind of easy little trick I am overlooking? Please advise...

Thank you so much for your consideration and help!!!

Moojid Hamid
Ranch Hand

Joined: Mar 07, 2009
Posts: 120
First of all JSP is not a place you should be accessing your database from, please use JSP for View of MVC only.

You can solve the problem by using java.sql.PreparedStatements instead of java.sql.Statement to insert the data into database.
Ma New

Joined: Sep 22, 2008
Posts: 12
Why do you say I should only use JSP for the MVC? With all due respect, I don't understand why they would make all kinds of tools to access a database from JSP using services such as the SQL portion of JSTL if you were to only supposed to use JSP for the MVC. Additionally, how would I use the prepared statement to fix the problem of things like apostrophes. Like I said I am new to Java Servlets and JSP so I am just trying to figure out how all this stuff works. My reference material is all in book for with people who all have differing opinions of how you should use this kind of technology. Thank you for your prompt response.

Kindest regards,

Jan Cumps

Joined: Dec 20, 2006
Posts: 2543

(skipping the MVC part, and reverting to the original question)
Additionally, how would I use the prepared statement to fix the problem of things like apostrophes.
Moojid has pointed you to the right approach.

if you create an SQL statement with PreparedStatement, you bind your values to the query, in stead of inserting them.
Magically, all your apostrophe problems are gone. No escapes hassle or anything.
It's not difficult. Give it a try.

OCUP UML fundamental and ITIL foundation
Ma New

Joined: Sep 22, 2008
Posts: 12
According to my JavaDocs for JSTL all SQL executions are done in a PreparedStatement. Here is the following information from the Javadoc:

public interface SQLExecutionTag
This interface allows tag handlers implementing it to receive values for parameter markers in their SQL statements.

This interface is implemented by both <sql:query> and <sql:update>. Its addSQLParameter() method is called by nested parameter actions (such as <sql:param>) to substitute PreparedStatement parameter values for "?" parameter markers in the SQL statement of the enclosing SQLExecutionTag action.

The given parameter values are converted to their corresponding SQL type (following the rules in the JDBC specification) before they are sent to the database.

Keeping track of the index of the parameter values being added is the responsibility of the tag handler implementing this interface

The SQLExcecutionTag interface is exposed in order to support custom parameter actions which may retrieve their parameters from any source and process them before substituting them for a parameter marker in the SQL statement of the enclosing SQLExecutionTag action

Justyna Horwat

How is it that the JSTL method would have issues inserting?
Paul Clapham

Joined: Oct 14, 2005
Posts: 19719

What makes you think that the JSTL has "issues" with inserting?

(Remember, you're the only one in this conversation who has seen the error messages.)
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: Inserting Apostrophes Into MySQL
jQuery in Action, 3rd edition