Creating table using java in mysql

Question

I have a simple project where at the end of user registration it gets to create a specific table by the name of that user in mySql Database.

After connecting to DB. Function for ExecutingUpdate:

public String UpdateDBQuery(String query) throws IOException{
    try {
        int ursa = st.executeUpdate(query);
        if (ursa == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        } else {
            return "succeed";
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

Calling function:

String DBOutput = connect.UpdateDBQuery("CREATE TABLE '"+UserName+"' (ID INT(20) AUTO_INCREMENT PRIMARY KEY, PROFILEIMAGES VARCHAR(100), REG_DATE timestamp)");

But my code above through an exception saying:

com.mysql.jdbc.exceptions.jdbc4.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 ''jasdfhkjhasdkfj' (ID INT(20) AUTO_INCREMENT PRIMARY KEY, PROFILEIMAGES VARCHAR(' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)

Although I searched through internet but couldn't find the problem.

all replies are much appreciate:)

These type of queries are more prone to Sql Injection, and here in this case your value for string `UserName` is `'jasdfhkjhasdkfj` — Noushad, Oct 15 '16 at 12:11
Thank you very much about informing me about the vulnerability I might save the username with someothere name but could you tell me what is the problem which is showing the exception? — jack, Oct 15 '16 at 12:14
@piyushMittal yeah I can execute the query works fine and I also have tested the query at mysql editor and works but not in my javaee code — jack, Oct 15 '16 at 12:16
https://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection — Danh, Oct 15 '16 at 12:21
This is a design that can't scale. Users should not be creating tables; you should create a schema into which they can add rows. Rethink your problem. — duffymo, Oct 15 '16 at 12:35

score 0 · Answer 1 · answered Oct 15 '16 at 12:15

0

I think you need to look at MYSQL and databases in general. you're trying to create a table for a single user.

I would suggest you look up some tutorial for databases and sql beforehand. You want to have a user table where you add rows for each user, not creating a new table for each user.

answered Oct 15 '16 at 12:15

Christian Moen

1,253
2
17
31

Thanks for bring that up, see in my project user could come an upload their file and what I do is to save those file and save the location of the file in my db, see I don't think i might be able to create 1000 columns for 1000 files can i? – jack Oct 15 '16 at 12:20
and also i might not be able to create 10000 columns in a single table, thought it might be better to create a specific table for each user – jack Oct 15 '16 at 12:22
If you want to associate some data to the user, you actually use the users primary key as an Foreign key in another table (of the table you wanna store the file in). – Christian Moen Oct 15 '16 at 12:23
and what if you have 2000 user? – Khalil M Oct 15 '16 at 12:24
1

Of what you've said to me, this image will display how to do it properly [Look at this ER-diagram for a database](https://i.gyazo.com/42b157a37eacc91f87f0aecf760813f2.png) – Christian Moen Oct 15 '16 at 12:28
hey is it possible to create one table and one column for each user and save each location on that row FYI: I don't know how much user would get registered at my site probably 1 or 1000000... – jack Oct 15 '16 at 12:29
You can have as many rows you want. A table is not beeing indexed. So you'd have a hard time searching in it. – Christian Moen Oct 15 '16 at 12:30
and what about column can I have as many column as I want and is it easy to search it too. – jack Oct 15 '16 at 12:32
You can, but you might want to have them in different tables. As you might encounter the use of one to Many, or many to many. – Christian Moen Oct 15 '16 at 12:33

Creating table using java in mysql

1 Answers1