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
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
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;
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();
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.
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.
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.
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?