MySQL Syntax error in Java prepared statement

**kc120us** · March 20th, 2012, 10:45 PM

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.

**Sean4u** · March 21st, 2012, 06:03 PM

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?

**kc120us** · March 21st, 2012, 06:35 PM

Originally Posted by Sean4u

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.

**copeg** · March 21st, 2012, 07:45 PM

Originally Posted by kc120us

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

**kc120us** · March 22nd, 2012, 11:31 AM

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

Thread: MySQL Syntax error in Java prepared statement

LinkBack

Thread Tools

Display

MySQL Syntax error in Java prepared statement

Related threads:

Re: MySQL Syntax error in Java prepared statement

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

Re: MySQL Syntax error in Java prepared statement

Re: MySQL Syntax error in Java prepared statement

Re: MySQL Syntax error in Java prepared statement

Similar Threads

MySQL Syntax Error

Syntax error with dispose()

do while syntax error problem

unable to execute prepared statement

Prepared Statement exceptions please help