Prevent SQLite database injection.

Discussion in 'Plugin Development' started by zeoed, Aug 12, 2011.

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

    zeoed

    I am using alta189's SQLite database library to connect to a status database. All someone would have to do with my plugin is say "/sstatus hello there;--drop table status;" to delete the table. Is their anyway to fix this problem by sanitizing the input before sending it to alta's SQLite library?

    Code:
            if (command.getName().equalsIgnoreCase("sstatus")) {
                if (!(sender instanceof Player)) {
                    sender.sendMessage("This command must be executed by a player.");
                    return true;
                }
                //TODO: Set the players status in the SQLite file
                String query = "SELECT COUNT(*) as count FROM status WHERE `user` = '" + player.getName() + "'";
                ResultSet result = dbManage.sqlQuery(query);
                int count = 0;
                try {
                    count = result.getInt("count");
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
    
                if (count == 1) {
                    //TODO: Update Query
                    String tempNewString = "";
                    for (String item : args){
                        tempNewString = tempNewString + " " + item;
                    }
                        String updatequery = "UPDATE status SET `status`='" + tempNewString + "' WHERE `user`='" + player.getName() + "';";
                        dbManage.updateQuery(updatequery);
                        player.sendMessage(ChatColor.GREEN + "Your status has been updated successfully.");
                } else {
                    //TODO: Insert Query
                    String tempNewString = "";
                    for (String item : args){
                        tempNewString = tempNewString + " " + item;
                    }
                    String insertquery = "INSERT INTO status (user, status) values ('" + player.getName() + "','" + tempNewString + "');";
                    dbManage.insertQuery(insertquery);
                    player.sendMessage(ChatColor.GREEN + "Your status has been updated successfully.");
                }
                //TODO:  Handle command status
                return true;
            }
     
  2. Of course, just check whether the string that is passed contains forbidden items.
     
  3. Offline

    desht

    Manually sanitising SQL input strings is not a great solution. It's error prone compared to the virtually bulletproof solution of using prepared SQL statements. Unfortunately @alta189's library doesn't seem to support that right now. I would recommend rolling your own prepared statements for now. Looking at the code, you can do something like:

    PHP:
    import com.alta189.sqlLibrary.SQLite.sqlCore;
    // ...

    public sqlCore manageSQLite;
    // ...
    manageSQLite.initialize();
    PreparedStatement stmt manageSQLite.getConnection().prepare("select x, y, z from table where x = ? and y = ? and z = ?");
    stmt.set(1x);
    stmt.set(2y);
    stmt.set(3z);
    ResultSet rs stmt.executeQuery();
     
  4. Offline

    zeoed

    What I did was use Base64 to encode what I wanted to insert into the database. Much simpler :)
     
Thread Status:
Not open for further replies.

Share This Page