C# SQLite crashing if input is wrong

Question

I've got an problem with an Windows Forms Program: The user should be able to choose which table(year) they want to see.

That's my solution:

SQLiteConnection con = new SQLiteConnection(@"Data Source=C:\App\DRSTZMSTR\Datenbank\Database.db");
con.Open();
string query = "SELECT* from '"+yeartxtbox.Text+"' ";
SQLiteCommand cmd = new SQLiteCommand(query, con);
DataTable dt = new DataTable();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(dt);

dataGridView1.DataSource = dt;
con.Close();

I'm using a textbox "yeartxtbox" to receive the wanted table. Basically its working, but every time the user inputted table does not exist the program crashes.

Any idea how I could fix this?

My thoughts:

Instead of using a textbox I could use an combobox to display the existing tables, but I have no ide how to realize this. I couldn't find anything online.

I don't believe you can put the table name in apostrophes. Try backticks (\`) if you actually have year numbers for table names (but seriously reconsider that design). — madreflection, Apr 14 '22 at 16:17
You could redo your query to check this first *before trying to access it*, just a thought. — Trevor, Apr 14 '22 at 17:09

pm100 · Accepted Answer · 2022-04-14T16:38:48.750

0

Try / catch

try {
   SQLiteConnection con = new SQLiteConnection(@"Data Source=C:\App\DRSTZMSTR\Datenbank\Database.db");
   con.Open();
   string query = "SELECT* from '"+yeartxtbox.Text+"' ";
   SQLiteCommand cmd = new SQLiteCommand(query, con);
   DataTable dt = new DataTable();
   SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
   adapter.Fill(dt);

   dataGridView1.DataSource = dt;
   con.Close();
}
catch (Exception e){
   // do whatever you want to tell the user they made a mistake - eg
   MessageBox.Show(e.ToString());
}

Note that this type of SQL string composition is extremely bad thing to do. I can enter "table1; delete * from table1" as the table name. Its called "SQL Injection"

If you want to query the database to get a list of tables you can do this

SELECT name, sql FROM sqlite_master
WHERE type='table'
ORDER BY name;

see SQLite Schema Information Metadata

edited Apr 14 '22 at 16:38

answered Apr 14 '22 at 16:17

pm100

48,078
23
82
145

Why not handle the correct exception `SQLiteException`, there's much more information from that then the `Exception` class. – Trevor Apr 14 '22 at 17:08
CA1031: Do not catch general exception types https://learn.microsoft.com/en-us/dotnet/fundamentals/code-analysis/quality-rules/ca1031 – Shleemypants Apr 15 '22 at 01:10
@Trevor because I didnt have a test system and could not recall what exceptions were thrown. I hoped OP would work out that simply slamming the raw exception in the users face was not a good idea – pm100 Apr 15 '22 at 01:20

score 0 · Answer 2 · answered Apr 14 '22 at 16:32

0

Your approach is wrong by allowing the user to type in a textbox and that is why the user can type a table name that does not exist. Instead of a textbox, use a combo box that an item can only be selected from a list of items that exist in the database.

In order to populate the combo box, change your query to :- string query = "Select [ID],[Name] from sysobjects where xtype = 'U'";

Get the result into your datatable and bind your Combo box to that datatable , then the user can only see the list of existing tables and select anyone.

Note:- sysobjects is a table in every SQL Server database that contains all the objects. xType column = 'U' means you are looking at only tables. That should resolve your problem.

answered Apr 14 '22 at 16:32

Raphael Adeniji

26
2

1

there is no standard way to query the schema of a sql database , sysobjects is SQL server. SQLIte has this https://www.sqlite.org/schematab.html – pm100 Apr 14 '22 at 16:36
What I used as example is SQL Server, Oracle also has its own. I want to also believe SQLite will also have a table within the database where all objects' details are stored. All you need to do is to change the query line to look at their own table . For instance, if it is Oracle database, the query will look like Select tabname from tab – Raphael Adeniji Apr 14 '22 at 17:28

C# SQLite crashing if input is wrong

2 Answers2