C# - Windows Forms - SQLite - SQL Statement Syntax

Question

this is my Code. I want to check if the account is activated. In my database, there is a column "activated". If there is the value NULL the account is not activated. If there is a 1 it is activated. But if I add a value to the variable "username" the error:

"SQLite error (1): no such column: Lucas in "SELECT benutzername, aktivierung FROM anmeldung WHERE benutzername=Lucas AND aktivierung IS NULL"

string username = txtbox_benutzername.Text;
        lookActivate(username);
        if (variable.aktivierung == 1)
        {
            MessageBox.Show("variable.aktivierung");
            variable.aktivierung = 0;
        }
public static void lookActivate(string username)
    {
        try
        { 
            string cs = @"URI=file:C:/Users/lucas/source/repos/Anmelddung/Anmelddung/database.db";

            var con = new SQLiteConnection(cs);
            con.Open();
            const string quote = "\"";
            string stm = "SELECT benutzername, aktivierung FROM anmeldung WHERE benutzername=" + username + " AND aktivierung IS NOT NULL";
            Console.WriteLine(stm);
            var cmd = new SQLiteCommand(stm, con);
            SQLiteDataReader rdr = cmd.ExecuteReader();


            while (rdr.Read())
            {
                username = rdr.GetString(0);
                variable.aktivierung = rdr.GetInt32(1);
                Console.WriteLine(username, variable.aktivierung);
                MessageBox.Show($"{rdr.GetString(0)} {rdr.GetInt32(1)}");
            }
        }
        catch
        {
            MessageBox.Show("Ihr Konto wurde wohl noch nicht von einem Administrator freigegeben");
        }
    }
    static class variable
    {
        public static int aktivierung;
    }

please try and make the title of your question a question

Keith Nicholas
– Keith Nicholas

2020-08-02 21:48:27 +00:00
Commented Aug 2, 2020 at 21:48 — Keith Nicholas
– Keith Nicholas, Commented Aug 2, 2020 at 21:48

GMB · Accepted Answer · 2020-08-02 23:25:35Z

5

Your code fails because you are missing single quotes around the string that you are concatenating into the sql query - so SQLite takes it for a column name rather than a literal string.

You should just use a parameterized query. This handles escaping for you under the hood, prevents SQL injection, and makes your code simpler and more efficient:

var con = new SQLiteConnection(cs);
con.Open();
string stm = "SELECT benutzername, aktivierung FROM anmeldung WHERE benutzername= ? AND aktivierung IS NOT NULL";
Console.WriteLine(stm);
var cmd = new SQLiteCommand(stm, con);
cmd.Parameters.Add(username, SqlDbType.VarChar);
SQLiteDataReader rdr = cmd.ExecuteReader();

You might need to adapt the sql type of your parameter according to the actual datatype of your database column.

edited Aug 2, 2020 at 23:25

answered Aug 2, 2020 at 21:28

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Caius Jard Over a year ago

What SQLite library are you using that has an SqlLiteParameterCollection with an Add overload that takes a single string value?

Collectives™ on Stack Overflow

C# - Windows Forms - SQLite - SQL Statement Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related