Problems with " and '

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?

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.

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.

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.

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

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;



      }

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.

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.

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 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.

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.

Thanks for everyones help. It all works perfectly.