SQLite syntax error inside of command: 'SQL logic error near "=": syntax error'

Question

I keep getting the exception

System.Data.SQLite.SQLiteException: 'SQL logic error near "=": syntax error'

and I have no idea why or whats causing it :(

my code is as follows

SQLiteConnection c = new SQLiteConnection("Data Source = StuTchInfo.db");
SQLiteCommand cmd = new SQLiteCommand(c);
cmd.CommandText = "SELECT *" +
    "FROM tblTeachers" +
   $"WHERE teacherID = '{idField.Text.ToString()}'";
c.Open();
SQLiteDataReader r = cmd.ExecuteReader();

//code processing data from data reader

and the exception is thrown on the last line. idField.Text is a string value and I have already tried using idField.Text.ToString() but it still throws the same error. Any ideas?

Does this answer your question? [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) and [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — , Aug 10 '21 at 09:51
Besides not using string concatenation and bad formatting/indentation, here it is a typo: a space is missing before WHERE, thus write for example `$" WHERE ..."`. — , Aug 10 '21 at 09:53

Caius Jard · Answer 1 · 2021-08-10T11:01:37.567

Your string concatenation is causing an sql that contains tblTeachersWHERE

If you want to keep a multi line format for readability, you can ditch the string concat by using an @string

cmd.CommandText = @"
  SELECT *
  FROM tblTeachers
  WHERE teacherID = @name";

//you'll need this too; gives a value to the above parameter
cmd.Parameters.AddWithValue("@name", idField.Text);

You can also investigate Resources facility (right click on project name and choose Properties, then Resources on the left, and "click there" if your project doesnt have one); it allows you to write long strings like huge SQLs and not clutter your code with them. Code would be like:

cmd.CommandText = Properties.Resources.SelectFromTeachersWhereNameIs;

and your designer for the Resources be like:

As a side note, investigate how to parametrize your queries (I did it above with @name), because even if you fix this your code will explode again as soon as someone enters a ' into the textbox, or worse

score 2 · Accepted Answer · answered Aug 10 '21 at 09:48

2

You need to add space.

cmd.CommandText = "SELECT * " +
    "FROM tblTeachers " +
    $"WHERE teacherID = '{idField.Text.ToString()}'";

answered Aug 10 '21 at 09:48

Misha Zaslavsky

8,414
11
70
116

1

oh wow that was really it idk how i forgot string concatenation doesn't automatically put a space – budywudy9 Aug 10 '21 at 09:52
Sql won't care about `SELECT *FROM`, btw – Caius Jard Aug 10 '21 at 09:53
1

@CaiusJard Good to know, but anyway it would be good to have this space for better readability. – Misha Zaslavsky Aug 10 '21 at 09:58
Verbatim string is much easier, preface with `@`, then you can use newlines – Charlieface Aug 10 '21 at 11:20

score 1 · Answer 3 · answered Aug 10 '21 at 09:48

1

You need to use some spaces in your string. Also, you can have one-liner code by using string interpolation.

cmd.CommandText = $"SELECT * FROM tblTeachers WHERE teacherID = '{idField.Text.ToString()}'";

answered Aug 10 '21 at 09:48

Mihai Alexandru-Ionut

47,092
13
101
128

1

I'll probably try doing this from now on at least until i get more used to SQL commands considering I managed to forget string concatenation doesn't automatically add spaces – budywudy9 Aug 10 '21 at 09:52

SQLite syntax error inside of command: 'SQL logic error near "=": syntax error'

3 Answers3