HELP WITH MYSQL - Error: Unknown column 'Falcon_Seeker' in 'field list'

Discussion in 'Spigot Plugin Development' started by FalconSeeker, Aug 1, 2018.

  1. I'm fairly new to mysql, so I am using an api at the moment while i learn it.
    The error is that
    Code (Text):
    [15:49:13 INFO]: MySQL Update:
    [15:49:13 INFO]: Command: INSERT INTO playerdata (players) VALUES (Falcon_Seeker)
    [15:49:13 INFO]: Error: Unknown column 'Falcon_Seeker' in 'field list'
    Here is my code.
    Code (Text):
            String s = e.getPlayer().getName();
            SQL.insertData("players", s, "playerdata"); //NOT WORKING
            SQL.insertData("online", String.valueOf(Bukkit.getOnlinePlayers().size()), "playerdata"); //-WORKING ONE
    The "online" column works, but players doesnt.
    This is where I create it.
    Code (Text):
                SQL.createTable("playerdata", "players VARCHAR(11), chat INT (11), online INT (11), join_date TIMESTAMP");          
     
    PLZ help. This is where the mysql api is.
    Code (Text):
      public static void insertData(String columns, String values, String table)
      {
        MySQL.update("INSERT INTO " + table + " (" + columns + ") VALUES (" + values + ")");
      }
     
    #1 FalconSeeker, Aug 1, 2018
    Last edited: Aug 1, 2018
  2. You're trying to use a column called. "falcon_seeker" but that's not a thing.
     
  3. But the other one works and i dont have a column called 1. (# of online players)
     
  4. Strings in SQL always have to be surrounded by quotes. So instead of inserting Falcon_Seeker you should insert 'Falcon_Seeker'

    Also look into prepared statements, they are way more secure and easier to use :)
    And dont forget to close any connections and statements you open! They'll easily create massive server-crashing memory leaks (I speak from experience :p)
     
  5. So this would work?
    Code (Text):
            SQL.insertData("players", "'" + s + "'", "playerdata");
     
     
  6. Yes.

    But again, you should really look into preparedstatements, you'll just be able to do .setString(#, string), so much easier, less chance of injection, etc.
     
  7. PreparedStatements will prevent SQLInjection (something that in 2018 should be erradicated...) and it also type formats for you (i.e. no quotes needed contrary to what @simgar98 said, although he IS correct in saying strings must have quotes).

    E.g. if I have a String and a PreparedStatement like this:

    INSERT INTO table (column) VALUES (?)

    I can replace the ‘?’ with my string. Notice how I didn’t add quotes? This is becasuse PreparedStatement does it for me: and if it was an integer it wouldn’t add quotes. That’s another reason for it; if you change data types, you don’t have to change your statement.

    The method is:
    Code (Java):
    preparedStatement.setObject(int index, Object value);
    However, remember that the int index starts from 1, not 0!

    So, for this example it would be something like this:
    Code (Java):

    String columnValue = /* get the value from somewhere */

    PreparedStatement statement = connection.prepareStatement("INSERT INTO table (column, column2) VALUES (?, ?)");
    statement.setObject(1, columnValue); /* This is a String, will have quotes */
    statement.setObject(2, 54); /* This is an int, will not have quotes */
     
    Also, as @simgar98 said, you must close your Connections and PreparedStatements. Do this with try-with-resource blocks:
    Code (Java):

    try(final Connection connection = connect(); final PreparedStatement stmt = connection.prepareStatement("COMMAND HERE")) {
        stmt.setObject(.......);
        stmt.executeQuery();
        /* This will close the connection & the prepared statement. */
    }
     
     
    • Like Like x 2
    • Informative Informative x 1
  8. so this would be correcT?

    Code (Text):

    public void setSQL(String command)
    {
        try
        {
            Connection con = DriverManager.getConnection("localhost...", "user", "password");
            PreparedStatement statement = con.prepareStatement(command);
            statement.executeQuery();
            con.close();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
    }
    then
    Code (Text):
            SQL.setSQL("INSERT IGNORE INTO playerdata (players) VALUES (" + s + ")");
     
     
    #8 FalconSeeker, Aug 1, 2018
    Last edited: Aug 1, 2018
  9. So is this good?
    Code (Text):
    public void setSQL(String columnValue, int object)
    {
        try
        {
            Connection con = DriverManager.getConnection("localhost:---/----", "p---", "--");
            PreparedStatement statement = con.prepareStatement("INSERT INTO playerdata (players) VALUES (?)");
            statement.setObject(object, columnValue);

            statement.executeQuery();
            con.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
    }
     
  10. yea, imagine it as a string.replace() but with extra steps.
     
  11. You should close your prepared statement. Use a finally block in case an error occurs when closing resources, or better yet use a try-with-resources
     
    • Agree Agree x 1