?
Solved

Problems with " and '

Posted on 2003-02-22
13
Medium Priority
?
210 Views
Last Modified: 2010-03-31
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?
0
Comment
Question by:coincidence
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +2
13 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 8000318
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?
0
 

Author Comment

by:coincidence
ID: 8000359
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.
0
 

Author Comment

by:coincidence
ID: 8000362
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.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:coincidence
ID: 8000364
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.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 8000565
If you use a PreparedStatement to do your insert, it will take care of the single quotes for you.
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8000573
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;



      }

0
 
LVL 18

Expert Comment

by:bobbit31
ID: 8000590
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.
0
 
LVL 2

Expert Comment

by:functionpointer
ID: 8004128
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.
0
 

Author Comment

by:coincidence
ID: 8021711
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.
0
 

Author Comment

by:coincidence
ID: 8021733
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.
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 200 total points
ID: 8021793
This is how you should do it (assuming these *are* the names of your table columns):

PreparedStatement ps = con.prepareStatement("INSERT INTO MYTABLE(intNumber,strMyTitle,strMainText,intStatus) VALUES(?,?,?,?)");
ps.setString(1, theNumber);
ps.setString(2, theTitle);
ps.setString(3, theText);
ps.setString(4, theStatus);
ps.executeUpdate();

All your double and single quotes will be taken care of for you.
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8021887
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.
0
 

Author Comment

by:coincidence
ID: 8026497
Thanks for everyones help. It all works perfectly.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question