Solved SQL bug.

So during my testing of one of my Plugins using SQL, I encountered an annoying bug. I'm sure it's a simple fix, however I am a little new to SQL.

The function of this section of the plugin is to log the player's chat messages. This is successful except for my little problem, and I think it might be due to how modified something.

Here is my error:








You can see at the very top of the stacktrace at 10:59:24 where the chat was logged successfully.
However at the bottom of the stacktrace you can see the message that started causing the problem.
It starts when I use " ' " in any chat message.

Here is my chat table. You can see that the chat message at 10:59:50 was not logged.


The stacktrace is indicating an error at line 57 of my Functions.java class, (I wrote a custom method for logging the chat) Here is that section of code:

Code:java

 public void logPlayerChat(Player p,String msg, String sql){
 
        String name = p.getName();
        String ip = "" +p.getAddress();
        UUID uuid = p.getUniqueId();
 
        sql = sql.replaceAll("%name%", p.getName());
        sql = sql.replaceAll("%ip%",""+ p.getAddress());
        sql = sql.replaceAll("%uuid%", "" +p.getUniqueId());
        sql = sql.replaceAll("%msg%", msg);
        sql = sql.replaceAll("%date%", "" +ts.stamp); //
        try {
          main.sql.c.prepareStatement(sql).executeUpdate(); //Line 57 is here.
            Bukkit.getServer().getConsoleSender().sendMessage("Logged the chat for " +p.getName());
            //LogToConsole(sql_prefix +" &7Logged the chat for &bplayer, &8"+name);
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

Now on my Main class I have my chat Listener. Here is where I call this method:

Code:java

 @EventHandler
    public void onPlayerChat(AsyncPlayerChatEvent event) {
        Player p =event.getPlayer();
 
 
        f.logPlayerChat(p,event.getMessage(), "INSERT INTO chat (name, UUID, chat, stamp) VALUES ('%name%', '%uuid%', '%msg%', '%date%' );");
 
    }

If I missed anything let me know.

Thanks for any help that can be provided.

 

If you would use the Named Parameters in your prepared statements instead of using String.replaceAll() it would solve the problem.

 

No, no, no!
Please, do use a PreparedStatement object. Like this:

Code:

PreparedStatement st = yourConnection.prepareStatement("INSERT INTO chat(name, UUID, chat, stamp) VALUES (?, ?, ?, ?)");
st.setString(1, name);
// Your other thingies
st.execute();
st.close();

 

So...

Code:java

PreparedStatement myStatement  = conection.prepareStatement("INSERT INTO chat(name, UUID, chat, stamp) VALUES (?, ?, ?, ?)");
 
    myStatement.setString(1, p.getName());
    myStatement.setString(2,p.getUUID());
    myStatement.setString(3, p.getMessage());
    myStatement.setString(4, timestamp);
 
    myStatement.execute();
    st.close();

That's what I originally thought. I will test this out.

Thanks to you both, this has been resolved! Lolmewn hexaan

Lolmewn Do you have an recourses for learning SQL that you can suggest to me? Books, Docs?

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

I did most of it following the MySQL references, actually.
Here's a nice reason why to use PreparedStatements (it's faster!).
If you don't know something, Google is really your friend That's how I did it, really.

 

Lolmewn Yeah google is amazing, I just didn't realize there was a more efficient way. Thanks!

 

Lolmewn
Most of the books I have about SQL and Databases are in other languages. I would suggest you google MySQL tutrial and look for a good link! There is so much more that can be done with databases, but also so much that can go wrong. If you ever have a question about mysql just pm me and I'll see if I can help!

 

hexaan Are you specifically addressing me or the OP here?

 

Lolmewn Hahaha Sorry yeah I was trying to Tahg himXD