1

EDIT :: closing resultset and statements for queries solved the problem for me

I am trying to build a chat app by java and sqlite. when i try to insert a new record to a table in data base i am facing data base locked error message. here i try to add new user when they sign up first time:

public void addNewUser(String username,String password)  {
    StringBuilder sb=new StringBuilder("INSERT INTO loginInfo VALUES('");
    sb.append(username);
    sb.append("','");
    sb.append(password);
    sb.append("')");
    try {
        Statement statement = connection.createStatement();

        statement.execute(sb.toString());
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

i am first checking if already same username existed in doesUserExist() function:

private void handleSignup(String[] tokens) throws IOException, SQLException {
    if(tokens.length==3){
        String username=tokens[1];
        String password=tokens[2];
        String msg=null;
        if(!dataSource.doesUserExists(username)) {
            msg="signup successful\n";
            this.login=username;
            dataSource.addNewUser(username,password);
        }
        else msg="signup unsuccessful\n";
        this.send(msg);
    }
}

i get this following error message:

org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)
at org.sqlite.core.DB.newSQLException(DB.java:1010)
at org.sqlite.core.DB.newSQLException(DB.java:1022)
at org.sqlite.core.DB.execute(DB.java:861)
at org.sqlite.core.CoreStatement.exec(CoreStatement.java:80)
at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:53)

i have closed the statement after execution of all other queries. what can i do to solve this bug?

Redwanul Karim
  • 11
  • 3
  • We need more code to give an answer. Also, DO NOT SEND RAW STRINGS TO YOUR DATABASE!!!! Your chat app will last about 5 minutes and you will be a victim of SQL injection. Seriously, fix this. https://owasp.org/www-community/attacks/SQL_Injection – mikeb Oct 12 '20 at 20:13
  • is using prepared statements enough to avoid injections? i have added some more code – Redwanul Karim Oct 12 '20 at 20:42
  • Please add how you are creating your connection to your question, and read https://stackoverflow.com/questions/13891006/getting-sqlite-busy-database-file-is-locked-with-select-statements – tgdavies Oct 12 '20 at 23:35

1 Answers1

0

You have some other process or thread that is writing in the database and is holding RESERVED lock, or you have open and not closed TRANSACTION.

http://www.sqlite.org/draft/matrix/lockingv3.html

Writing to a database file

Only one process at a time can hold a RESERVED lock. But other processes can continue to read the database while the RESERVED lock is held.

If the process that wants to write is unable to obtain a RESERVED lock, it must mean that another process already has a RESERVED lock. In that case, the write attempt fails and returns SQLITE_BUSY.

https://sqlite.org/lang_transaction.html

Any command that accesses the database (basically, any SQL command, except a few PRAGMA statements) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last SQL statement finishes.

Ivo Gjorgjievski
  • 129
  • 3