MySQL - Object not null?

Question

I am trying to check if a player is already is in the database with this code:

Statement sql = mySql.getConnection().createStatement();
ResultSet check = sql.executeQuery("SELECT * FROM `playerinfo` WHERE Username='" + player.getName() + "';");
System.out.println(check.toString());
if(check != null) {
    System.out.println("2");
    Bukkit.getConsoleSender().sendMessage(ChatColor.RED + "Player already in database");
    check.close();
    sql.close();
    return;
}

I checked but nothing is in the database and it says that the player already contains in the database Sorry for bad english

First thing to do: start using parameterized SQL instead of the SQL Injection Attack vulnerability you've got now. — Jon Skeet
– Jon Skeet, Commented Apr 26, 2015 at 15:24
check will not be null because you're assigning a value to it. Check the contents of the result set instead, e.g. with check.next(). — Mick Mnemonic
– Mick Mnemonic, Commented Apr 26, 2015 at 15:27
How many rows are in your check ResultSet? You're not checking that. — Andrew Henle
– Andrew Henle, Commented Apr 26, 2015 at 15:28

Mick Mnemonic · Accepted Answer · 2015-04-26 16:41:37Z

Some considerations:

When checking whether the database contains a certain value, it's good practise to do this using a query that returns a single value (and not SELECT * which returns all columns of all rows that match the WHERE condition). You can do this e.g. by selecting a single check flag (SELECT 1) with a row-limiting clause (LIMIT 1):

SELECT 1 FROM playerinfo WHERE Username = ? LIMIT 1

This query is guaranteed to return only one row (with a single column, '1') if a player with the given name exists, or no rows if there are no players with the given name.

As others have pointed out, when you're inputting parameters into the query, you should use a PreparedStatement instead of a simple statement with concatenated inputs. This way, you can avoid SQL injection and the database is also able to reuse/cache the query (or cursor) internally.

Finally, you should close the resources you use, even if an Exception gets thrown during the execution. This is best done in the finally clause, or if you're on Java 7 or later, using the try-with-resources statement.

With these things in mind, a re-write of your code could look like this:

PreparedStatement ps = null;
try {
    ps = mySQL.getConnection()        
        .prepareStatement("SELECT 1 FROM playerinfo WHERE Username = ? LIMIT 1");
    ps.setString(1, player.getName()); 
    ResultSet rs = ps.executeQuery();

    // the first invocation of rs.next() returns true if 
    // there are rows in the result set, or false if no rows were found
    if (rs.next()) {
        System.out.println("2");
        Bukkit.getConsoleSender().sendMessage(ChatColor.RED 
            + "Player already in database");
    }        
    rs.close();
} finally {
    if (ps != null) {
        ps.close();
    }
}

ptntialunrlsd · Accepted Answer · 2015-04-26 15:30:57Z

0

I think instead of checking if the ResultSet is null or not, you should check if the ResultSet contains any row or not.

Apart from that, use PreparedStatements.

answered Apr 26, 2015 at 15:30

ptntialunrlsd

7949 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL - Object not null?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related