Implementing MySQL - Need Some Help

Discussion in 'Plugin Development' started by MCGamerTechz, May 21, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    MCGamerTechz

    So I need some help with implementing a MySQL database into my plugin, I followed this tutorial
    just for the sole purpose to see how MySQL works in a plugin and I got as far as creating a table it if does not exist. Then when I want to update the table on my block break event, I get an error saying that the username "gamertechzone" - my username does not exist in where clause. I am using this resource to connect to my mysql database.

    My mysql connection class:
    Code:java
    1. package me.GamerTechZone.Bookworm;
    2.  
    3. import java.sql.ResultSet;
    4. import java.sql.SQLException;
    5. import java.sql.Statement;
    6.  
    7. import org.bukkit.entity.Player;
    8.  
    9. import code.husky.mysql.MySQL;
    10.  
    11.  
    12.  
    13. public class MysqlManager {
    14. private final Main main;
    15. private MySQL db;
    16.  
    17. public MysqlManager(Main b)
    18. {
    19. this.main = b;
    20. }
    21.  
    22. public void setupDB() throws SQLException
    23. {
    24. this.db = new MySQL(this.main, "localhost", "3306", "plugindatabase", "root", "");
    25. this.db.openConnection();
    26. Statement statement = this.db.getConnection().createStatement();
    27. statement.executeUpdate("CREATE TABLE IF NOT EXISTS applications (Name varchar(32), Uses int)");
    28. }
    29.  
    30. public void closeDB()
    31. {
    32. this.db.closeConnection();
    33. }
    34.  
    35. public int getUses(Player p) throws SQLException
    36. {
    37. String pname = p.getDisplayName().toLowerCase();
    38. if(!this.db.checkConnection())
    39. this.db.openConnection();
    40. Statement statement = this.db.getConnection().createStatement();
    41. ResultSet rs = statement.executeQuery("SELECT * FROM applications WHERE Name = "+ pname +";");
    42.  
    43. if(!rs.next())
    44. return 0;
    45. return rs.getInt("Uses");
    46.  
    47. }
    48.  
    49. public void updatePlayer(Player p) throws SQLException
    50. {
    51. String pname = p.getDisplayName().toLowerCase();
    52. if(!this.db.checkConnection())
    53. this.db.openConnection();
    54. Statement statement = this.db.getConnection().createStatement();
    55. int uses = this.getUses(p);
    56. if(uses != 0)
    57. {
    58. statement.executeQuery("UPDATE applications SET Uses = "+ (uses+1) +" WHERE Name = "+ pname +";");
    59. }else{
    60. statement.executeQuery("INSERT INTO applications (Name, Uses) VALUES ("+ pname +", 1);");
    61. }
    62. }
    63. }
    64.  


    And my block listener class:
    Code:java
    1. package me.GamerTechZone.Bookworm;
    2.  
    3. import java.sql.SQLException;
    4.  
    5. import org.bukkit.event.EventHandler;
    6. import org.bukkit.event.Listener;
    7. import org.bukkit.event.block.BlockBreakEvent;
    8.  
    9. public class BlockListener implements Listener{
    10. private final Main main;
    11.  
    12. public BlockListener(Main bL)
    13. {
    14. this.main = bL;
    15. }
    16.  
    17. @EventHandler(ignoreCancelled = true)
    18. public void onBlockBreak(BlockBreakEvent e)
    19. {
    20. if(this.main.logDB)
    21. {
    22. try {
    23. this.main.mysql.updatePlayer(e.getPlayer());
    24. } catch (SQLException e1) {
    25. System.out.println("Could not update player: " + e.getPlayer().getDisplayName());
    26. System.out.println("Error: " + e);
    27. e1.printStackTrace();
    28. }
    29. }
    30. }
    31.  
    32. }
    33.  


    My onEnable:
    Code:java
    1. public void onEnable()
    2. {
    3. try {
    4. this.mysql.setupDB();
    5. } catch (SQLException e) {
    6. this.logDB = false;
    7. System.out.println("Could not connect to database");
    8. System.out.println("Error: " + e);
    9. e.printStackTrace();
    10. }
    11. this.getServer().getPluginManager().registerEvents(this, this);
    12. this.getServer().getPluginManager().registerEvents(bl, this);
    13. }


    So if someone could point on the problem, that would be great!
     
  2. Offline

    TGRHavoc

    MCGamerTechz
    I think it's because you're not putting the data into the database in the first place... You need to check whether the table has the player's name (Prefereably UUID now, because of the 1.8 update) and, if it doesn't create a new entry for them..
     
  3. Offline

    MCGamerTechz

    I
    I do this here:
    1. Code:java
      1. if(uses != 0)
      2. {
      3. statement.executeQuery("UPDATE applications SET Uses = "+ (uses+1) +" WHERE Name = "+ pname +";");
      4. }else{
      5. statement.executeQuery("INSERT INTO applications (Name, Uses) VALUES ("+ pname +", 1);");
      6. }

      }
     
  4. Offline

    Everdras




    Be careful when using executeQuery("sql goes here " + var + " more sql");

    This can lead to something called SQL injection. What if the player is named "john, 1); DROP TABLE applications; #"?

    Use prepared statements instead, so you don't have to worry about this.
     
Thread Status:
Not open for further replies.

Share This Page