System.Data.SQLite.SQLiteException: 'SQLite error no such column: aa'

Question

Im trying to insert data into my sqlite database which I have previously created, in vb.net:

    Using objConn As New SQLiteConnection(/my path to file is here/)

        Using objCommand As SQLiteCommand = objConn.CreateCommand()

            objConn.Open()

            objCommand.CommandText = "INSERT INTO users (username , password ) VALUES ( " + usernamevar + ",  " + passwordvar + " )"

            objCommand.ExecuteNonQuery()

            MessageBox.Show("User created successfully")

        End Using

    End Using

Usernamevar and passwordvar are values which I pass into the sub above.

However, I'm receiving an error message:

System.Data.SQLite.SQLiteException: 'SQLite error no such column: aa'

With "aa" being string passed under variable Usernamevar.

I realise I must have a syntax error somewhere in the insert phrase because I can successfully insert strings or numbers into the database, however I want to insert variables. Does anyone know how to fix it? Thanks.

The problem is that you're using input values as code in your SQL statement. Which is not only a syntax error in this case, but a huge security vulnerability in other cases. Values should be query parameters: devart.com/dotconnect/sqlite/docs/… — David
– David, Commented Dec 9, 2018 at 16:41

Mary · Accepted Answer · 2018-12-10 02:18:26Z

2

You can add single quotes around the 2 string variables or you can learn to use parameters which will save you from these details and protect your databse.

objCommand.CommandText = "INSERT INTO users (username , password ) VALUES ( '" + UserNameVar + "',  '" + Passwordvar + "' )"

The easier and better way

Private Sub InsertUser(UserNameVar As String, Passwordvar As String)
    Using objConn As New SQLiteConnection("My path To file Is here")
        Using objCommand As SQLiteCommand = objConn.CreateCommand()
            objCommand.CommandText = "INSERT INTO users (username , password ) VALUES (@UserName @Password);"
            objCommand.Parameters.Add("@UserName", DbType.String).Value = UserNameVar
            objCommand.Parameters.Add("@Password", DbType.String).Value = Passwordvar
            objConn.Open()
            objCommand.ExecuteNonQuery()
            MessageBox.Show("User created successfully")
        End Using
    End Using
End Sub

I can't believe I am providing and answer with .AddWithValue but I can't seem to find an alternative where data type can be added to a method. I guess the broad implementation of types in SQLite makes it somewhat irrelevant.

EDIT Changed to .Add method which allows me to give the data type. Thank you to @ WelcomeOverflow for showing me the way.

edited Dec 10, 2018 at 2:18

answered Dec 9, 2018 at 21:52

Mary

15.1k3 gold badges20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ňɏssa Pøngjǣrdenlarp Over a year ago

cmd.parameters.Add("@user", DbType.String).Value = varUName. leaving SQLite to guess at the type is especially dangerous because it is serverless so it can be easy to destroy the DB by saving binary to a text column for instance. That SQLite has limited DB Types is mostly an implementation detail

Collectives™ on Stack Overflow

System.Data.SQLite.SQLiteException: 'SQLite error no such column: aa'

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related