SQL is trying to create a table from the values given using Java

Question

I have an issue where I am trying to insert 3 values into a table in a local postgres database.

I have this code that deals with the sql here:

try {
    Class.forName("org.postgresql.Driver");
    c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/javadb","postgres", "mypass");
    stmt = c.createStatement();
    String sql = "CREATE TABLE GAMES " +
                    "(GAME_NAME    TEXT   PRIMARY KEY    NOT NULL, " +
                    " PRICE               FLOAT   NOT NULL, " +
                    " URL                 CHAR(800))";
    String sql2 = "INSERT INTO GAMES (GAME_NAME, PRICE, URL) "
                    + "VALUES (" + this.GAME_NAME + ", " +
                    Float.toString(this.PRICE) + ", " +
                    this.URL + ");";
    stmt.executeUpdate(sql);
    stmt.executeUpdate(sql2);
    stmt.close();
} catch (Exception e) {
    e.printStackTrace();
}

And the Constructor here:

public class UploadToDB {
    String GAME_NAME, URL;
    float PRICE;
    public UploadToDB(String GAME_NAME, float PRICE, String URL) {
            this.GAME_NAME = GAME_NAME;
            this.PRICE = PRICE;
            this.URL = URL;
    }
}

When I run this code, I get this error:

org.postgresql.util.PSQLException: ERROR: column "cod" does not exist

I assume this means it is trying to insert something into a column named after the values.
How can I run this so that it actually inserts the values given into the columns?

score 0 · Answer 1 · answered Feb 01 '18 at 00:13

0

Add quotes around TEXT and CHAR type values.

answered Feb 01 '18 at 00:13

kTest

367
1
11

I get a syntax error when I try that. I tried with both ' ' and " ". – wanderer0810 Feb 01 '18 at 00:16

wanderer0810 · Answer 2 · 2018-02-01T02:17:02.607

-1

Ok, I figured it out.

The values give needed to have ' around them.

Updated Code:

try {
    Class.forName("org.postgresql.Driver");
    c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/javadb","postgres", "mypass");
    stmt = c.createStatement();
    String sql = "CREATE TABLE GAMES " +
                    "(GAME_NAME    TEXT   PRIMARY KEY    NOT NULL, " +
                    " PRICE               FLOAT   NOT NULL, " +
                    " URL                 CHAR(800))";
    String insert = "INSERT INTO customer(GAME_NAME,PRICE,URL) VALUES(this.GAME_NAME, this.PRICE, this.URL);";
    PreparedStatement ps = connection.prepareStatement(insert);
    ps.setString(1, GAME_NAME);
    ps.setString(2, PRICE);
    ps.setString(3, URL);
    stmt.executeUpdate(sql);
    stmt.executeUpdate(ps);
    stmt.close();
} catch (Exception e) {
    e.printStackTrace();
}

Edit: Changed the way the the sql was done to prevent a SQL Injection attack as mentioned by Scary Wombat.

edited Feb 01 '18 at 02:17

answered Feb 01 '18 at 00:41

wanderer0810

400
1
7
20

really you should be using a `PreparedStatement` or similar - code such as yours opens the possibility of an SQL Injection attack - see https://stackoverflow.com/questions/9516625/prevent-sql-injection-attacks-in-a-java-program – Scary Wombat Feb 01 '18 at 01:21
@ScaryWombat Thank you for bringing this up! I'll change that right away. – wanderer0810 Feb 01 '18 at 01:34
Can I ask why I was downvoted so I can improve my posts in the future? – wanderer0810 Feb 01 '18 at 01:35
*I'll change that right away* and when you do I will re-consider my downvote – Scary Wombat Feb 01 '18 at 02:08
@ScaryWombat Changed. Again, thank you for letting me know about the vulnerability! – wanderer0810 Feb 01 '18 at 02:25
This code will **not** work as expected, test your code before posting it here – Scary Wombat Feb 01 '18 at 02:27

SQL is trying to create a table from the values given using Java

2 Answers2