Prevent SQLite database injection.

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;
        }

 

Of course, just check whether the string that is passed contains forbidden items.

 

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(1, x);
stmt.set(2, y);
stmt.set(3, z);
ResultSet rs = stmt.executeQuery();


 

What I did was use Base64 to encode what I wanted to insert into the database. Much simpler