Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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?
0
coincidence
Asked:
coincidence
  • 6
  • 2
  • 2
  • +2
1 Solution
 
bobbit31Commented:
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
 
coincidenceAuthor Commented:
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
 
coincidenceAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
coincidenceAuthor Commented:
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
 
CEHJCommented:
If you use a PreparedStatement to do your insert, it will take care of the single quotes for you.
0
 
allahabadCommented:
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
 
bobbit31Commented:
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
 
functionpointerCommented:
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
 
coincidenceAuthor Commented:
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
 
coincidenceAuthor Commented:
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
 
CEHJCommented:
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
 
allahabadCommented:
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
 
coincidenceAuthor Commented:
Thanks for everyones help. It all works perfectly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now