• Post Reply Bookmark Topic Watch Topic
  • New Topic

Prepared statement for create table query  RSS feed

 
Zulfi Khan
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.








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
Sheriff
Posts: 4288
127
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looks like you are getting an SQL error, right?  So remove this line: and replace it with then post the complete error message here.

Speaking of complete, please post the complete code, with import and package statements.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 37513
554
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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:


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
Sheriff
Posts: 21135
87
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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):
 
Zulfi Khan
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Thanks for your information. I have solved this problem. God bless you and all others on this forum.




This time there is no exception:


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


 
Brian Tkatch
Bartender
Posts: 598
26
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 22841
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 598
26
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 598
26
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 22841
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 598
26
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!