Forum:

JDBC and Relational Databases

Trouble with "@" and spaces in mySQL

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hello,
I'm using a servlet to write records to a mysql database running on my server. The problem i'm having is that everytime the servlet hits a space or an "@" symbol it kicks me back an SQL exception. Is there a certain data type i should use in mySQL or do i just have to parse all the text boxes and and escape out all the Spaces and @ symbols. Any help is greatly appreciated.
Thanks,
Brian

Ron Newman

Ranch Hand

Posts: 1056

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Can you show us some code that demonstrates the problem? Maybe a very simple servlet that inserts some data and generates this exception?

Ron Newman - SCJP 1.2 (100%, 7 August 2002)

Brian K Swingle

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

sure,
Here is the page i'm working with. give it a try.
http://plpadmin.tempdomainname.com/ServerCard.html
give it a try. I think my problem is maybe my data types in MYsql. I'm using phpMYadmin to administer the database.let me know.
Thanks,
Brian

Ron Newman

Ranch Hand

Posts: 1056

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I don't see a link to your servlet or JDBC code there; where is it?
[ July 24, 2003: Message edited by: Ron Newman ]

Ron Newman - SCJP 1.2 (100%, 7 August 2002)

Brian K Swingle

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

sorry that is the page that calls the servlet you can test it to see the exception using that but here is the code.

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.Hashtable;
 
public class ServerCard extends HttpServlet {
 
  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws IOException, ServletException { 
    PrintWriter out = response.getWriter();
    response.setContentType("text/html");
    try {
      //--------------------Instantiating User Information Strings-----------------
      Date today = new Date();
      String Date1 = today.toString();
      String CompanyName = request.getParameter ("CompanyName");
      String Address1 = request.getParameter ("Address1");
      String Address2 = request.getParameter ("Address2");
      String City = request.getParameter ("City");
      String State = request.getParameter ("State");
      String Zip = request.getParameter ("Zip");
      String Country = request.getParameter ("Country");
      String CName = request.getParameter ("CName");
      String Phone1 = request.getParameter ("Phone1");
      String Email1 = request.getParameter ("Email1");
      String AltCName = request.getParameter ("AltCName");
      String Phone2 = request.getParameter ("Phone2");
      String Email2 = request.getParameter ("Email2");
      String WebAddress = request.getParameter ("WebAddress");
      String Product = request.getParameter ("Product");
      String SerialNum = request.getParameter ("SerialNum");
      String Make = request.getParameter ("Make");
      String Location = request.getParameter ("Location");
      String TransNum = request.getParameter ("TransNum");
      // The call to Class.forName explicitly loads the driver class
      Class.forName("org.gjt.mm.mysql.Driver").newInstance();
      Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost/UpgradeFinder?user=****&password=***");
      if (conn != null) 
        out.println("Connection Made");
      Statement stmt = conn.createStatement();
      String query = 
          "INSERT INTO ServerCard (CompanyName,Address1,Address2,City,State,Zip," + 
          "Country,CName,Phone1,Email1,AltCName,Phone2,Email2,WebAddress,Product," + 
          "SerialNum,Make,Location,TransNum,Date,RecNum) VALUES("+CompanyName+","+
          Address1+","+Address2+","+City+","+State+","+Zip+","+Country+","+CName+","+
          Phone1+","+Email1+","+AltCName+","+Phone2+","+Email2+","+WebAddress+","+
          Product+","+SerialNum+","+Make+","+Location+","+TransNum+","+Date1+",)";
      ResultSet rs = stmt.executeQuery(query);
      out.println("<html>");
      out.println("<HEAD>");
      out.println("</HEAD>");
      out.println("<BODY  link='#d10e1b' vlink='#d1040d' alink='#d10000' " + 
          "leftmargin='15' marginheight='15' marginwidth='15' topmargin='15'>");
      out.println("   <div align='left'>");
      out.println("     <table width='570' border='0' cellspacing='2' cellpadding='8' " + 
          "bgcolor='#c4c4c4' height='200'>");
      out.println("       <tr>");
      out.println("         <td bgcolor='#e5e5e5'>");
      out.println("           <div align='center'>");
      out.println("             <font size='2' color='maroon' face='Arial,Helvetica," + 
          "Geneva,Swiss,SunSans-Regular'><b>PowerLeap Server Kit Warranty " + 
          "Registration Is Complete!</b></font><br>");
      out.println("             <br>");
      out.println("             <b><font size='1' face='Arial,Helvetica,Geneva," + 
          "Swiss,SunSans-Regular'>Thank You for choosing PowerLeap Upgrades." + 
          "</font><font size='1'><br>");
      out.println("               </font></b>");
      out.println("             <p></b></p>");
      out.println("           </div>");
      out.println("         </td>");
      out.println("       </tr>");
      out.println("     </table>");
      out.println("     <table width='571' border='0' cellspacing='2' cellpadding='0' " + 
          "bgcolor='#efefef'>");
      out.println("       <tr>");
      out.println("         <td>");
      out.println("           <div align='center'>");
      out.println("             <font size='1' face='Arial,Helvetica,Geneva,Swiss," + 
          "SunSans-Regular'><a href='Products.html'>Products</a> | " + 
          "<a href='http://powerleap.custhelp.com/cgi-bin/powerleap.cfg/php/enduser/" + 
          "home.php' target='_self'>Support</a> | <a href='Gaming.htm' " + 
          "target='_self'>Gaming</a> | <a href='Corporate.html' " + 
          "target='_self'>Corporate</a> | <a href='GovEdu.html' " + 
          "target='_self'>Gov. & Edu.</a> | <a href='Resellers.html'" + 
          ">Reseller</a> | <a href='AboutUs.html' target='_self'>About " + 
          "Us</a> | <a href='ContactUs.html' target='_self'>Contact Us" + 
          "</a> </font></div>");
      out.println("         </td>");
      out.println("       </tr>");
      out.println("     </table>");
      out.println("   </div>");
      out.println(" </BODY>");
      out.println("</html>");
    }
    catch( Exception e ) {
      
     out.println(e);
    }
    out.println("</body>");
    out.println("</html>");
  }
}

let me know if you need anything else from me. Thanks,
Brian
[ July 24, 2003: Message edited by: Brian K Swingle ]
[ edited to preserve formatting using the [code] and [/code] UBB tags and to break apart long lines -ds ]
[ July 25, 2003: Message edited by: Dirk Schreckmann ]

Brian K Swingle

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

One more thing just to add to this i went into the phpMYadmin tool and using the SQL window i was able to incert a record with spaces and a "@" symbol with no problem. So somethings weired here i just cant put my finger on it. Thanks for all of your help so far. Let me know what you think.
Thanks
Brian

Ron Newman

Ranch Hand

Posts: 1056

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

This code is very dangerous. You are not quoting any of the strings that come from the user form, so syntax errors are inevitable.
Even worse things could happen: suppose the user enters something like this into one of the form fields?
foo); drop table ServerCard;
You should use a PreparedStatement to avoid these problems.

Ron Newman - SCJP 1.2 (100%, 7 August 2002)

Brian K Swingle

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I'm relatively inexperienced programmer could you further explain to me what you mean by "quoting" and the benefits there of. and do you have an example of a servlet that uses these methods. I would like to learn the best and safest way to go about inserting user data into a database. Thanks for all your help.
Thanks,
Brian

Ron Newman

Ranch Hand

Posts: 1056

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Your generated SQL statement needs to look like

and not

You're going to need to write a static method, something like this:
static String quote(String s) {
return ("'" + s.replace("'","''") + "'");
}
and pass all of the input strings through it before you put them into your SQL statement. There really should be such a static method in the JDBC library; I don't know why there isn't.
A better idea is to use a PreparedStatement, which handles this all for you.

Ron Newman - SCJP 1.2 (100%, 7 August 2002)

Brian K Swingle

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

ah i think i get it. So the single tics cause the
information to be treated as a literal and not inturperated in any way. So that most likely whats causing my problem. I'll give that a try.
Thanks,
Brian

Brian K Swingle

Ranch Hand

Posts: 39

posted 21 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

It worked perfectly thank you for all your help.
Thanks,
Brian Swingle