Avatar of coincidence
coincidence
 asked on

Problems with " and '

I have a jsp variable that is created by a user entering data into a text area. On the nex t page another JSP page uses a getParameter to obtain the  data entered into the previously entered data. The data is transfered to a  HSQL database. I am having major problems with the code generating  errors :

A standard SQL type exception was encountered: java.sql.SQLException: Unexpected token:

when a user uses a '  (single quote) or " (double quote). Is there a way to get around this?

Does anyone have a solution to this problem? Either through Javascript or throgh a JSP solutions itself?
Java

Avatar of undefined
Last Comment
coincidence

8/22/2022 - Mon
bobbit31

if the problem w/ your query?  the common way to get around this is to replace single quote w/ two single quotes

ie: String strSQL = "Insert into table (field1, field2) values ('bobbit''s comment', 'bobbit''s comment 2')";

don't know if this is exactly your problem... if you could clarify please?
coincidence

ASKER
well the problem is I can do what you say when I enter my own data. However when a  user is entering data I can not force them to enter the double single quote.

for example if a user enters:

it's a long way to the front...

instead of :

it is a long way to the front

I can not stop them!

I am having problems taking that data in java and reformatting the data in a way that java or javascript doesn't have get upset.
coincidence

ASKER
well the problem is I can do what you say when I enter my own data. However when a  user is entering data I can not force them to enter the double single quote.

for example if a user enters:

it's a long way to the front...

instead of :

it is a long way to the front

I can not stop them!

I am having problems taking that data in java and reformatting the data in a way that java or javascript doesn't have get upset.
Your help has saved me hundreds of hours of internet surfing.
fblack61
coincidence

ASKER
well the problem is I can do what you say when I enter my own data. However when a  user is entering data I can not force them to enter the double single quote.

for example if a user enters:

it's a long way to the front...

instead of :

it is a long way to the front

I can not stop them!

I am having problems taking that data in java and reformatting the data in a way that java or javascript doesn't have get upset.
CEHJ

If you use a PreparedStatement to do your insert, it will take care of the single quotes for you.
allahabad

Use can create a method get the value to pass in insert statement. This method will append all '(single quote) to '' , to insert the string into database.

public static String formatTextForInsert ( String txt) {


         String retText = txt;
         int txtlength = txt.length();
         int temp =0;

         int startPos = 0;

         while (startPos < txtlength ) {

            int singleQuotePos = txt.indexOf("\'",startPos);

            if (singleQuotePos == -1) {

               return retText;
            }
            else if (singleQuotePos >=0) {

               retText = retText.substring(0,singleQuotePos+temp) + "\'\'"
                        + retText.substring(singleQuotePos+1+temp,txtlength+temp);
               temp++;
            }

            startPos = singleQuotePos+1;

         }

         return retText;
      }


Use can create a method to format the retreived value from the database for display, if you using javascript .
This will format all "(double quote) present in string which will give error displaying the value through javascript.

public static String formatForDisplay ( String txt) {


         StringTokenizer tokenizer = new StringTokenizer(txt + " ", "\n");
         StringBuffer value = new StringBuffer();
         String theMsg = null;
         //Tokenize the message and place it into the array.
         while(tokenizer.hasMoreTokens())
         {
            theMsg = tokenizer.nextToken();

            if (tokenizer.hasMoreTokens() )
               value.append( theMsg.substring(0,theMsg.length() - 1) ).append("\\n");
            else if (theMsg == "") value.append( theMsg ).append(" ");
            else
               value.append( theMsg );
         }

         String text = value.toString();

         String retText = text;
         int txtlength = text.length();

         int temp =0;

         int startPos = 0;

         while (startPos < txtlength ) {

            int doubleQuotePos = text.indexOf("\"",startPos);

            if (doubleQuotePos == -1) {

               return ("\""+retText+"\"");
            }
            else if (doubleQuotePos >=0) {

               retText = retText.substring(0,doubleQuotePos+temp) + "\\\""
                        + retText.substring(doubleQuotePos+1+temp,txtlength+temp);
               temp++;
            }

            startPos = doubleQuotePos+1;

         }

         return retText;



      }

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bobbit31

as CEHJ states, prepared stament is probably your best bet.

PreparedStatement ps = con.prepareStatement("Select * from table where field1 = ? and field2 = ?");

ps.setString(1, "blah's");
ps.setString(2, "blah's's's");

ps.execute();


however, you can also do it the first way, by just doing:
String str1 = "it's a long way to the front";

String strSQL = "SELECT * from table where blah = '" + str1.replace("'", "''") + ...

and it should work fine.
functionpointer

take CEHJ and bobbit31's advice. You are potentially letting the endUser make sql queries to your database. What are you going to do when some malicious bastard finds your page on the  internet and enters:

' into bsTable
delete from yourVeryImportantTable where id<>0
insert into bsTable(value) VALUES ('

into your text box? he ends the first half of your query, makes his own, and builds a new head for the rest of your query. you gladdly snatch this up and the user, unbeknownst to you, has built 3 queries from your one, and you can kiss your data goodbye. Been there done that. ;-)
Unregulated Statement.executeQuery( String ) 's based on user input really don't have a place in a JSP/DB app.
coincidence

ASKER
I am having a few problems testing  the advice since im currently using a free jsp host that can sometimes make it hard to test new code.

I am a  little confused still though. Would I be right in saying I need allahabad's suggestion to get all the single and double quotes out?

Also I am confused about the syntax of the prepared statement and how they were described above. What does the number represent in the setstring method.

Basically this is the update I want to make:

s.executeUpdate("INSERT INTO MYTABLE(intNumber,strMyTitle,strMainText,intStatus) VALUES ("+theNumber+",'"+theTitle+"','"+theText+"',"+theStatus+")");

Would I  need to grab the text from both theTitle and theText and run them through the while loop with stringtokenizers or would using a preparedstatement get rid of any problems that may arise, malicious or otherwise regardless of the amount of quotes placed in the text?  If so could someone show me what I would need to do change  the above into a "secure" entry for a database.

Thanks.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
coincidence

ASKER
I am having a few problems testing  the advice since im currently using a free jsp host that can sometimes make it hard to test new code.

I am a  little confused still though. Would I be right in saying I need allahabad's suggestion to get all the single and double quotes out?

Also I am confused about the syntax of the prepared statement and how they were described above. What does the number represent in the setstring method.

Basically this is the update I want to make:

s.executeUpdate("INSERT INTO MYTABLE(intNumber,strMyTitle,strMainText,intStatus) VALUES ("+theNumber+",'"+theTitle+"','"+theText+"',"+theStatus+")");

Would I  need to grab the text from both theTitle and theText and run them through the while loop with stringtokenizers or would using a preparedstatement get rid of any problems that may arise, malicious or otherwise regardless of the amount of quotes placed in the text?  If so could someone show me what I would need to do change  the above into a "secure" entry for a database.

Thanks.
ASKER CERTIFIED SOLUTION
CEHJ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
allahabad

You can use PrepareStatement , and it will take care of your ' quote.
for ex.
String sql = "INSERT INTO EMPLOYEE(first_name,last_name,age ) "+
                         " VALUES(?,?,?) ";
       PreparedStatement     prepareStmt = con.prepareStatement(sql);
// 1, parameter is for first_name which is varchar2, jdbc equivalent is String. Call setString method to set the value for this.            
prepareStmt.setString(1,"jon'yy");
// You can use java variable that you get from your JSP // like prepareStmt.setString(1,name);

// 2, paramter is for last_name, similar to first_name
// call setString method
            prepareStmt.setString(2,"Wal'k'e'r'");
// 3, parameter is for age, which is number type in table.JDBC
// equivalent is int, call setInt method set the value for this.
            prepareStmt.setInt(3,54);
// call this method to insert the rows.
           int result = prepareStmt.executeUpdate();

            System.out.println("No of Rows Inserted " + result);

Hope this helps.
coincidence

ASKER
Thanks for everyones help. It all works perfectly.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.