Prepared statement for create table query

[Zulfi Khan]

Hi,
I am trying to create a prepared statement for create table query. This means that I want to parameterize the table name. Some body please guide.

    

public class ReadExcelSheetMain4 {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
private static final String DB_URL = "jdbc:mysql://localhost/attendance";
   //  Database credentials
private static final String USER = "root";
private static final String PASS = "z";
private Connection conn = null;
//private Statement stmt = null;
PreparedStatement pStmt = null;
    /**
     * @param args the command line arguments
     */
 

    public void readExcel() throws BiffException, IOException {
		String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";
		FileInputStream fs = new FileInputStream(FilePath);
		Workbook wb = Workbook.getWorkbook(fs);

		// TO get the access to the sheet
		Sheet sh = wb.getSheet("Sheet1");
                
		// To get the number of rows present in sheet
		int totalNoOfRows = sh.getRows();
                JOptionPane.showMessageDialog(null, "KKK totalNoOfRows = " + totalNoOfRows);
		// To get the number of columns present in sheet
		int totalNoOfCols = sh.getColumns();
                JOptionPane.showMessageDialog(null, "GGG totalNoOfCols = " + totalNoOfCols);
                int row=8;
                int col=1;
                String rollNo=null;
                
                
                //for ( row = 8; row < totalNoOfRows; row++) {
                   rollNo = sh.getCell(col, row).getContents();
                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;
                   //if (isEmpty) 
                     // break;
                    try{
                          System.out.println("Comes Here1");
                           //STEP 2: Register JDBC driver
                           Class.forName("com.mysql.jdbc.Driver");

                           //STEP 3: Open a connection
                           System.out.println("Connecting to database...");
                           conn = DriverManager.getConnection(DB_URL,USER,PASS);

                           //STEP 4: Execute a query
                           System.out.println("Creating statement Testing 999 ..." + rollNo);
                           String sql;
                           sql = "Create table if not exists ? (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";
                           
                           pStmt = conn.prepareStatement(sql);
                           
                           
                           //sql ="Create table if not exists Test(id Integer)";
                            pStmt.setString(1, rollNo);
                            pStmt.executeUpdate();
                            System.out.println("Comes Here Testing 110");
                        }
                        catch(SQLException sqle) {
System.out.println("Error1");
                        
                        }
                        catch(ClassNotFoundException cnfe) {
System.out.println("Error2");
                        }
                        catch (Exception e) {
System.out.println("Error3");
                        }
                        finally{
                           try{
                                 if(conn!= null) 
                                 conn.close();
                               }
                        
                        catch(SQLException sqle) {
System.out.println("Error4");
                        }
                       }//finally

    }
    public static void main(String[] args)throws BiffException, IOException {
        // TODO code application logic here
        ReadExcelSheetMain4 obj = new ReadExcelSheetMain4();
        obj.readExcel();
    }
    

I am getting following error:

D:\java prog\2017Self\accessing excel sheet in java>java ReadExcelSheetMain4
Warning:  Polygon Object on sheet "Sheet1" not supported - omitting
Warning:  Polygon Object on sheet "Sheet1" not supported - omitting
Comes Here1
Connecting to database...
Creating statement Testing 999 ...2015-SE-001
Error1


Somebody please guide me why I am getting error message

Zulfi.

[Knute Snortum]

Looks like you are getting an SQL error, right?  So remove this line:

System.out.println("Error1");

and replace it with

sqle.printStackTrace();

then post the complete error message here.

Speaking of complete, please post the complete code, with import and package statements.

[Jeanne Boyarsky]

Zulfi,
JDBC doesn't allow using a prepared statement binding variable for table names or columns. So instead you'll have to validate the value yourself and use string concatentation.

[Zulfi Khan]

Hi,
Thanks for the response. I am attaching the complete code with providing SQL trace option in the catch block and the associated SQL exception message:

import java.io.*;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import javax.swing.*;
import java.sql.*;


/**
 *
 * @author HP
 */
public class ReadExcelSheetMain4 {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
private static final String DB_URL = "jdbc:mysql://localhost/attendance";
   //  Database credentials
private static final String USER = "root";
private static final String PASS = "z";
private Connection conn = null;
//private Statement stmt = null;
PreparedStatement pStmt = null;
    /**
     * @param args the command line arguments
     */
    public void readExcel() throws BiffException, IOException {
		String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";
		FileInputStream fs = new FileInputStream(FilePath);
		Workbook wb = Workbook.getWorkbook(fs);

		// TO get the access to the sheet
		Sheet sh = wb.getSheet("Sheet1");
                
		// To get the number of rows present in sheet
		int totalNoOfRows = sh.getRows();
                JOptionPane.showMessageDialog(null, "KKK totalNoOfRows = " + totalNoOfRows);
		// To get the number of columns present in sheet
		int totalNoOfCols = sh.getColumns();
                JOptionPane.showMessageDialog(null, "GGG totalNoOfCols = " + totalNoOfCols);
                int row=8;
                int col=1;
                String rollNo=null;
                
                
                //for ( row = 8; row < totalNoOfRows; row++) {
                   rollNo = sh.getCell(col, row).getContents();
                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;
                   //if (isEmpty) 
                     // break;
                    try{
                          System.out.println("Comes Here1");
                           //STEP 2: Register JDBC driver
                           Class.forName("com.mysql.jdbc.Driver");

                           //STEP 3: Open a connection
                           System.out.println("Connecting to database...");
                           conn = DriverManager.getConnection(DB_URL,USER,PASS);

                           //STEP 4: Execute a query
                           System.out.println("Creating statement Testing 999 ..." + rollNo);
                           String sql;
                           sql = "Create table if not exists ? (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";
                           
                           pStmt = conn.prepareStatement(sql);
                           
                           
                           //sql ="Create table if not exists Test(id Integer)";
                            pStmt.setString(1, rollNo);
                            pStmt.executeUpdate();
                            System.out.println("Comes Here Testing 110");
                        }
                        catch(SQLException sqle) {
System.out.println("Error1");
sqle.printStackTrace();
                        
                        }
                        catch(ClassNotFoundException cnfe) {
System.out.println("Error2");
                        }
                        catch (Exception e) {
System.out.println("Error3");
                        }
                        finally{
                           try{
                                 if(conn!= null) 
                                 conn.close();
                               }
                        
                        catch(SQLException sqle) {
System.out.println("Error4");
                        }
                       }//finally
//                     }//for

                   
                   //System.out.println("rollNo" + rollNo);
                   
                
                
                //System.out.print(sh.getCell(col, row).getContents() + "\t");
		/*for (int row = 0; row < totalNoOfRows; row++) {

			for (int col = 0; col < totalNoOfCols; col++) {
				System.out.print(sh.getCell(col, row).getContents() + "\t");
			}
			System.out.println();
		}*/
    }
    public static void main(String[] args)throws BiffException, IOException {
        // TODO code application logic here
        ReadExcelSheetMain4 obj = new ReadExcelSheetMain4();
        obj.readExcel();
    }
    
}

The output is:

Warning:  Polygon Object on sheet "Sheet1" not supported - omitting
Warning:  Polygon Object on sheet "Sheet1" not supported - omitting
Comes Here1
Connecting to database...
Creating statement Testing 999 ...2015-SE-001
Error1
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ''2015-SE-001' (id INTEGER not null primary key auto_increment, name varchar(30),' at line 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
        at ReadExcelSheetMain4.readExcel(ReadExcelSheetMain4.java:79)
        at ReadExcelSheetMain4.main(ReadExcelSheetMain4.java:122)

I have tried to use concatenation in my code:

import java.io.*;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import javax.swing.*;
import java.sql.*;


/**
 *
 * @author HP
 */
public class ReadExcelSheetMain5 {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
private static final String DB_URL = "jdbc:mysql://localhost/attendance";
   //  Database credentials
private static final String USER = "root";
private static final String PASS = "z";
private Connection conn = null;
private Statement stmt = null;
PreparedStatement pStmt = null;
    /**
     * @param args the command line arguments
     */
    public void readExcel() throws BiffException, IOException {
		String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";
		FileInputStream fs = new FileInputStream(FilePath);
		Workbook wb = Workbook.getWorkbook(fs);

		// TO get the access to the sheet
		Sheet sh = wb.getSheet("Sheet1");
                
		// To get the number of rows present in sheet
		int totalNoOfRows = sh.getRows();
                JOptionPane.showMessageDialog(null, "KKK totalNoOfRows = " + totalNoOfRows);
		// To get the number of columns present in sheet
		int totalNoOfCols = sh.getColumns();
                JOptionPane.showMessageDialog(null, "GGG totalNoOfCols = " + totalNoOfCols);
                int row=8;
                int col=1;
                String rollNo=null;
                
                
                //for ( row = 8; row < totalNoOfRows; row++) {
                   rollNo = sh.getCell(col, row).getContents();
                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;
                   //if (isEmpty) 
                     // break;
                    try{
                          System.out.println("Comes Here1");
                           //STEP 2: Register JDBC driver
                           Class.forName("com.mysql.jdbc.Driver");

                           //STEP 3: Open a connection
                           System.out.println("Connecting to database...");
                           conn = DriverManager.getConnection(DB_URL,USER,PASS);

                           //STEP 4: Execute a query
                           System.out.println("Creating statement Testing 999 ..." + rollNo);
                           String sql;
                           sql = "Create table if not exists" [b]+ rollNo+[/b] " (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";
                           
                           stmt = conn.createStatement();
                           
                           
                           //sql ="Create table if not exists Test(id Integer)";
                           
                            stmt.executeUpdate(sql);
                            System.out.println("Comes Here Testing 110");
                        }
                        catch(SQLException sqle) {
System.out.println("Error1 after modifying");
sqle.printStackTrace();
                        
                        }
                        catch(ClassNotFoundException cnfe) {
System.out.println("Error2");
                        }
                        catch (Exception e) {
System.out.println("Error3");
                        }
                        finally{
                           try{
                                 if(conn!= null) 
                                 conn.close();
                               }
                        
                        catch(SQLException sqle) {
System.out.println("Error4");
                        }
                       }//finally
//                     }//for

                   
                   //System.out.println("rollNo" + rollNo);
                   
                
                
                //System.out.print(sh.getCell(col, row).getContents() + "\t");
		/*for (int row = 0; row < totalNoOfRows; row++) {

			for (int col = 0; col < totalNoOfCols; col++) {
				System.out.print(sh.getCell(col, row).getContents() + "\t");
			}
			System.out.println();
		}*/
    }
    public static void main(String[] args)throws BiffException, IOException {
        // TODO code application logic here
        ReadExcelSheetMain5 obj = new ReadExcelSheetMain5();
        obj.readExcel();
    }
    
}

I am getting following output:

Warning:  Polygon Object on sheet "Sheet1" not supported - omitting
Warning:  Polygon Object on sheet "Sheet1" not supported - omitting
Comes Here1
Connecting to database...
Creating statement Testing 999 ...2015-SE-001
Error1 after modifying
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'exists2015-SE-001 (id INTEGER not null primary key auto_increment, name varchar(' at line 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317)
        at ReadExcelSheetMain5.readExcel(ReadExcelSheetMain5.java:84)
        at ReadExcelSheetMain5.main(ReadExcelSheetMain5.java:127)

Somebody please help me.

Zulfi.

[Rob Spoor]

Zulfi Khan wrote:

near 'exists2015-SE-001 (id INTEGER not null primary key auto_increment, name varchar(' at line 1

There is a space missing between exists and the closing string quotes. You may also need to put backticks (`) around your table name, because I think it contains characters that would otherwise not be allowed (the dashes):

"...not exists `" + rollNo + "` (id ..."

[Zulfi Khan]

Hi,
Thanks for your information. I have solved this problem. God bless you and all others on this forum.

import java.io.*;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import javax.swing.*;
import java.sql.*;


/**
 *
 * @author HP
 */
public class ReadExcelSheetMain6 {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
private static final String DB_URL = "jdbc:mysql://localhost/attendance";
   //  Database credentials
private static final String USER = "root";
private static final String PASS = "z";
private Connection conn = null;
private Statement stmt = null;
PreparedStatement pStmt = null;
    /**
     * @param args the command line arguments
     */
    public void readExcel() throws BiffException, IOException {
		String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";
		FileInputStream fs = new FileInputStream(FilePath);
		Workbook wb = Workbook.getWorkbook(fs);

		// TO get the access to the sheet
		Sheet sh = wb.getSheet("Sheet1");
                
		// To get the number of rows present in sheet
		int totalNoOfRows = sh.getRows();
                JOptionPane.showMessageDialog(null, "KKK totalNoOfRows = " + totalNoOfRows);
		// To get the number of columns present in sheet
		int totalNoOfCols = sh.getColumns();
                JOptionPane.showMessageDialog(null, "GGG totalNoOfCols = " + totalNoOfCols);
                int row=8;
                int col=1;
                String rollNo=null;
                String newRollNo = null; 
                
                
                //for ( row = 8; row < totalNoOfRows; row++) {
                   rollNo = sh.getCell(col, row).getContents();
                   newRollNo = rollNo.replace('-','_');
                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;
                   //if (isEmpty) 
                     // break;
                    try{
                          System.out.println("Comes Here1");
                           //STEP 2: Register JDBC driver
                           Class.forName("com.mysql.jdbc.Driver");

                           //STEP 3: Open a connection
                           System.out.println("Connecting to database...");
                           conn = DriverManager.getConnection(DB_URL,USER,PASS);

                           //STEP 4: Execute a query
                           System.out.println("Creating statement Testing 999 ..." + rollNo);
                           String sql;
                           sql = "Create table if not exists " + newRollNo+ " (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";
                           
                           stmt = conn.createStatement();
                           
                           
                           //sql ="Create table if not exists Test(id Integer)";
                           
                            stmt.executeUpdate(sql);
                            System.out.println("Comes Here Testing 110");
                        }
                        catch(SQLException sqle) {
System.out.println("Error1 after modifying");
sqle.printStackTrace();
                        
                        }
                        catch(ClassNotFoundException cnfe) {
System.out.println("Error2");
                        }
                        catch (Exception e) {
System.out.println("Error3");
                        }
                        finally{
                           try{
                                 if(conn!= null) 
                                 conn.close();
                               }
                        
                        catch(SQLException sqle) {
System.out.println("Error4");
                        }
                       }//finally
//                     }//for

                   
                   //System.out.println("rollNo" + rollNo);
                   
                
                
                //System.out.print(sh.getCell(col, row).getContents() + "\t");
		/*for (int row = 0; row < totalNoOfRows; row++) {

			for (int col = 0; col < totalNoOfCols; col++) {
				System.out.print(sh.getCell(col, row).getContents() + "\t");
			}
			System.out.println();
		}*/
    }
    public static void main(String[] args)throws BiffException, IOException {
        // TODO code application logic here
        ReadExcelSheetMain6 obj = new ReadExcelSheetMain6();
        obj.readExcel();
    }
    
}

This time there is no exception:

Comes Here1
Connecting to database...
Creating statement Testing 999 ...2015-SE-001
Comes Here Testing 110

[Brian Tkatch]

As Jeanne pointed out, a table name cannot be parameterized. This is by design, as the purpose of parameterization i for strict typing and parameter reuse, neither of which applies to a table.

Please do not use dynamic sql, and especially not for creating tables. This is completely insecure, and not only opens your code up to sql injection, but a bug can wreak havoc by creating random objects unintentionally.

Instead, look back at what you are trying to do. A solution that includes dynamic table creation is suspect to me. It is rare this should need to be done, and even then, a more secure method can likely be found. Perhaps if you share what it is you are trying to do, we can come up with a different solution.

[Dave Tolls]

Looking at one of the earlier posts it does seem tat the MySQL driver does do something with these DDL statements, as the error from the PreparedStatement version was:

"
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 ''2015-SE-001' (id INTEGER not null primary key auto_increment, name varchar(30),' at line 1
"

Now, I do remember something about this from years back (can't find a reference now), but I think it might be simply doing a text replace?
Still, not something you'd actually want to be using in the wild, so to speak.

[Zulfi Khan]

Hi,
<This is completely insecure, and not only opens your code up to sql injection, but a bug can wreak havoc by creating random objects unintentionally. >
Thanks for your response. I appreciate your concern and like to learn from you. Right now its just a exercise which i am thinking to give to the student but its a practical problem also which we are facing. Our attendance system is not working. We use Excel sheets & fill these sheets monthly and this attendance determines whether student can sit in the exam or not. So i have devised this plan to create a separate table for each student. The advantage is that we can check students' attendance with a simple select statement without a where clause. But manual creation of tables is impossible because there are thousands of students, so i am using dynamic technique. I believe that tables  created whether manually or programatically would behave in the same manner.

Please explain how it can create havoc or what is sql injection or how can i improve? If you dont have time, you can ignore my request. I wont mind. I know one table solution possible but then we have to use complex sql statements which is not easy for Bosses.

Zulfi.

[Paul Clapham]

Zulfi Khan wrote:what is sql injection

As far as this part of the question, there's plenty of information about SQL injection on the internet. I will just refer you to the Wikipedia article for a start: SQL injection.

And as Brian Tkatch pointed out, creating database tables from Java code is an unusual thing to do. Commonly your application will have a finite set of tables which are designed in advance, and then programming code is written to work with those tables. If it's necessary to change those tables (and it often is) then that's done by database people manually. Again, it's unusual to have to automate that sort of activity. So let me reiterate Brian's question: why does your application have to do this sort of thing?

[Brian Tkatch]

Zulfi Khan wrote:Please explain how it can create havoc or what is sql injection or how can i improve?

Thank you for giving me a good excuse to post this relevant comic. That is, Little Bobby Tables (explanation). In short (in your case), without strict typing on your variable, that is, a database-enforced restriction to demand it be a table name, any statement can be run. And with CREATE TABLE rights, it can be abused intentionally, or perhaps even unintentionally.

Zulfi Khan wrote:i have devised this plan to create a separate table for each student. The advantage is that we can check students' attendance with a simple select statement without a where clause. But manual creation of tables is impossible because there are thousands of students, so i am using dynamic technique.

There is a saying which i will paraphrase for the current situation: Simple, Cheap, Secure; pick any two. You have chosen Simple and Cheap, but for that you have given up Secure.

Zulfi Khan wrote:I know one table solution possible but then we have to use complex sql statements

Are you saying that adding "WHERE Student = ?" is complex? I would consider that pretty normal and pretty simple. Of course, that's just my opinion.

[Dave Tolls]

I don't see how a WHERE clause is more complex than someone having to remember individual tables.

In any case, why are you expecting them to write a WHERE clause?
That's your code's job.
The user should simply have to choose the student they are interested in from some table or list, or a simple search box by name, and the underlying code should then go off and pull up the relevant data.

These users should never have to have any idea of what the data structure is behind all this.

[Brian Tkatch]

Dave Tolls wrote:I don't see how a WHERE clause is more complex than someone having to remember individual tables.

I happen to agree with you, but as it is a matter of opinion (and perhaps, taste) he might consider it different. I am curious to hear his opinion. I'd love to come to a solution we can all appreciate.

[Paul Clapham]

A separate table for each student is feasible, I'm sure, even if there are thousands of students. And it does make it simpler to do a query for data about a particular student, that's true.

But as soon as you find you need a query which asks for data about more than one student, you're going to regret putting each student's data in a separate table.

Yeah, there's a design rule which says you shouldn't try to predict future requirements, just design for the current requirements and change the design as required when the future requirements come along. However rules are made to be broken and I'd suggest you bend that rule -- inquire around the user community and see whether people are likely to take the data and use it for multi-student queries.

[Brian Tkatch]

Paul Clapham wrote:But as soon as you find you need a query which asks for data about more than one student, you're going to regret putting each student's data in a separate table.

Nah. At worst, he's regret not having creating a view to UNION ALL them together. :) FWIW, that is the cheap man's way of getting a partitioned view, and can be extremely effective.