Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 5 of 5

Thread: MySQL Syntax error in Java prepared statement

  1. #1
    Member
    Join Date
    Sep 2011
    Location
    Nanuet, NY USA
    Posts
    33
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default MySQL Syntax error in Java prepared statement

    Hi,
    I am writing a servlet using NetBeans that finds scores from a mySQL database. I have a simple select using a prepared statement. But I get the following error.

    INFO: com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''csci1301'' at line 1

    I don't see what is wrong.

            response.setContentType("text/html;charset=UTF-8");
            PrintWriter out = response.getWriter();
            String ssn;
            String tableName;
            String studentName="";
            int score = 0;
            try {
                /*
                 * TODO output your page here. You may use following sample code.
                 */
                out.println("<html>");
                out.println("<head>");
                out.println("<title>Servlet Exercise39_7</title>");            
                out.println("</head>");
                out.println("<body>");
                ssn = request.getParameter("ssn");
                tableName = request.getParameter("course");
                String queryString = "select * from ? ";
     
                pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1, tableName);
                ResultSet rset = pstmt.executeQuery();
                while (rset.next()) {
                  if (rset.getString("ssn").equals(ssn)){
                     out.println(rset.getString(studentName) + " " + rset.getInt(score));
                  }
                }

    Any help would be appreciated.


  2. #2
    Super Moderator Sean4u's Avatar
    Join Date
    Jul 2011
    Location
    Tavistock, UK
    Posts
    637
    Thanks
    5
    Thanked 103 Times in 93 Posts

    Default Re: MySQL Syntax error in Java prepared statement

    The table name isn't a parameter - you can't do that with PreparedStatement. You can either build your query string first (injection danger), use a stored procedure of some kind (maybe), or take another look at your DB schema: do you really want one table per course?

  3. The Following User Says Thank You to Sean4u For This Useful Post:

    kc120us (March 21st, 2012)

  4. #3
    Member
    Join Date
    Sep 2011
    Location
    Nanuet, NY USA
    Posts
    33
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: MySQL Syntax error in Java prepared statement

    Quote Originally Posted by Sean4u View Post
    The table name isn't a parameter - you can't do that with PreparedStatement. You can either build your query string first (injection danger), use a stored procedure of some kind (maybe), or take another look at your DB schema: do you really want one table per course?
    Thank you for your help. I was able to get it working by creating the query string first by concatenating the select string with the table name variable and using a Statement instead of the PreparedStatement. What did you mean by 'injection danger'? The exercise says to have each course in a table where the table name is the course name.

  5. #4
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,102
    Thanks
    169
    Thanked 779 Times in 725 Posts
    Blog Entries
    5

    Default Re: MySQL Syntax error in Java prepared statement

    Quote Originally Posted by kc120us View Post
    What did you mean by 'injection danger'?
    See SQL injection - Wikipedia, the free encyclopedia

    As a simple example, imagine the following scenario with your database having a table named users...

     
    tableName = request.getParameter("course");
    String queryString = "select * from " + tableName;

    ...in which the value of tableName is the following: "course=users;drop table users;"

    In the context above this most likely would not work, but it opens the door wide for alternative approaches

  6. #5
    Member
    Join Date
    Sep 2011
    Location
    Nanuet, NY USA
    Posts
    33
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: MySQL Syntax error in Java prepared statement

    I see what you are saying. Thank you for the information and explaining the security issue.

Similar Threads

  1. MySQL Syntax Error
    By tarkal in forum What's Wrong With My Code?
    Replies: 6
    Last Post: November 30th, 2011, 02:29 PM
  2. Syntax error with dispose()
    By jagnat in forum AWT / Java Swing
    Replies: 1
    Last Post: October 14th, 2011, 11:00 AM
  3. do while syntax error problem
    By derekxec in forum What's Wrong With My Code?
    Replies: 5
    Last Post: July 1st, 2011, 06:30 PM
  4. unable to execute prepared statement
    By nrao in forum What's Wrong With My Code?
    Replies: 1
    Last Post: November 11th, 2010, 07:26 PM
  5. Prepared Statement exceptions please help
    By nrao in forum What's Wrong With My Code?
    Replies: 5
    Last Post: November 11th, 2010, 07:16 PM