sqlite in vb.net, can't find table even though it exists

Question

I'm using the following code (System.Data.SQLite within VB.net):

        Dim SQLconnect As New SQLite.SQLiteConnection()
        Dim SQLcommand As SQLiteCommand
        SQLconnect.ConnectionString = "Data Source=vault.db;"
        SQLconnect.Open()
        SQLcommand = SQLconnect.CreateCommand
        SQLcommand.CommandText = "INSERT INTO entries VALUES ('" & appinput.Text & "','" & userinput.Text & "','" & passinput.Text & "')"
        SQLcommand.ExecuteNonQuery()
        SQLcommand.Dispose()
        SQLconnect.Close()
        Me.Hide()

I get an error back that says it can't find the table "entries" I know the table exists because I can write to it via the command line through sqlite and through Autoit and can see it and edit it in the SQLite browser when I open the database. I don't understand why VB can't see it (I know it sees and opens the database file just fine). Any ideas?

Answer 1

Argh! There are 3 big issues in that code. Please update it like this to fix two of them:

Using cn As New SQLite.SQLiteConnection("Data Source=vault.db;"), _
      cmd As New SQLiteCommand("INSERT INTO entries VALUES (@AppInput, @UserInput, @PassInput)", cn)

    cmd.Parameters.AddWithValue("@AppInput", appinput.Text);
    cmd.Parameters.AddWithValue("@UserInput", userinput.Text);
    cmd.Parameters.AddWithValue("@PassInput", passinput.Text);

    cn.Open()
    cmd.ExecuteNonQuery()
End Using

This will prevent sql injection by parameterizing your query instead of substituting values directly and prevent db locking issues by making sure your connection is disposed properly, even if an exception is thrown.

The third problem is that you should NEVER store plain-text passwords in your database (or anywhere else for that matter). Go read up on how to hash values in .Net and hash and salt your password before storing or comparing it.

Once you've done that re-test your code to see if you still get the same errors reported as before. We need to make sure this didn't solve the problem or introduce something new. Then we can start addressing the missing table issue, perhaps by checking your connection string.

Answer 2

Most likely your problem is with relative paths (directories).

sqlite will create a database file if it does not exist so you will never get a "db file not found message". The first indication of an incorrect path is "table missing".

My personal experience is that although it goes against my programmers instinct is to alway use an absolute (fully qualified) path/file name for an sqlite database.

If you put in the full file location like "/var/myapp/vault.db" you should be OK. If this is likly to move around store pick up the file name from a properties/config file -- 'config file not found' is much easier to deal with than "table not found".