SqliteParameter setParam = new SqliteParameter ("@setValue", setValue);        SqliteParameter whereParam = new SqliteParameter ("@whereValue", whereValue);        dbCommand.Parameters.Add (setParam);        dbCommand.Parameters.Add (whereParam);

instead of doing this, you could actually do this

dbCommand.Parameters.AddWithValue("@setParam", setValue);dbcommand.Parameters.AddWithValue("@whereValue", whereValue);

that is how I would do with with SQL Server, I don't think it is any different with SqlLite.

also, I would do this a little differently as well

using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection)){    dbConnection.Open ();    using (var dbCommand = dbConnection.CreateCommand ())    {

I would instead write it like this

using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))using (var dbCommand = dbConnection.CreateCommand ()){    dbConnection.Open ();

it looks a lot cleaner and removes some of the nesting.

also, try to limit what you do inside the using statements, to whatever you actually need in the using statements and not more than that.

Another thing, don't spread your string onto 3 lines like that, it looks messy.

pull your SQL statement out of the using statements, and make it a simple string, then pass it into the constructor of the dbCommand.

this:

public void UpdateData (string tableName, string whereHeader, object whereValue, string setHeader, object setValue){    using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))    {        dbConnection.Open ();        using (var dbCommand = dbConnection.CreateCommand ())        {            dbCommand.CommandText = string.Format ("UPDATE {0} " +                                                   "SET {1} = @setValue " +                                                   "WHERE {2} = @whereValue",                tableName, setHeader, whereHeader);            SqliteParameter setParam = new SqliteParameter ("@setValue", setValue);            SqliteParameter whereParam = new SqliteParameter ("@whereValue", whereValue);            dbCommand.Parameters.Add (setParam);            dbCommand.Parameters.Add (whereParam);            Debug.Log (dbCommand.CommandText);            dbCommand.ExecuteNonQuery ();        }    }}

should look more like this

public void UpdateData (string tableName, string whereHeader, object whereValue, string setHeader, object setValue){    var sql = string.Format ("UPDATE {0} SET {1} = @setValue WHERE {2} = @whereValue", tableName, setHeader, whereHeader);    Debug.Log (sql.ToString());    using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))    using (var dbCommand = dbConnection.CreateCommand (sql, dbConnection))    {        dbConnection.Open ();        dbCommand.Parameters.AddWithValue("@setParam", setParam);        dbcommand.Parameters.AddWithValue("@whereValue", whereValue);        dbCommand.ExecuteNonQuery ();    }}

• \$\begingroup\$Thanks for the criticism! The only issue I have with this is my version of SQLite does not haveAddWithValue. So, unfortunately, I am stuck with creating them and adding them separately. In regards to cleaning my method up, the method you provided looks so much cleaner and I will take all of what you said into account when creating the rest of my methods. Do you think it is worth me following up on prepared statements?\$\endgroup\$

  Mike Hunt

  – Mike Hunt

  2016-03-11 20:45:39 +00:00

  CommentedMar 11, 2016 at 20:45
• 1
  \$\begingroup\$I am not sure that prepared statements are going to work for you with SQLite... you can look into it, it won't hurt to know about them either way.\$\endgroup\$

  Malachi

  – Malachi

  2016-03-11 21:12:31 +00:00

  CommentedMar 11, 2016 at 21:12

To address one aspect in particular:

I attempted to have SQL Injection prevention parameters fortableName,selectHeader andwhereHeader, however, it kept throwing an error saying the syntax was wrong. I'm assuming this can't be done then.

Good remark. Yes, this cannot be done, by design (read: it should not be done this way, ideally). What you are trying to do can seem like a good idea on the surface, to allow you to dynamically write an arbitrary SQL query on the fly by passing it DB object identifier names, and evenSQL executable code as object parameters.

However, this is avery bad idea in almost 100% of cases (with the very rare edge cases where you purposefullywant to inject SQL to execute, which should always be done withextreme prudence).

Here are a few reasons why you shouldn't use this method in production code:

1. Potential SQL injection vulnerability, obviously;

2. Queries are not compiled (which is part of what preparing statements does) and therefore ran "ad hoc" against the database each time;

3. Prepared statements must know about the database objects they run on before they are executed, safeguarding against SQL errors at compile time instead of throwing errors from the database at run time.

4. Compiled queries arere-usable so this will increase performance.

5. Debugging dynamic SQL queries is much more difficult than static queries.

Ideally, you would want to write prepared queries that are specific to a single purpose. This takes more time to write the code, but in the end you will be much better off for the reasons stated above.

You should read theSqlCommand.Prepare documentation. I also found an article by Visual Studio Magazine calledSQLite Performance and Prepared Statements which looks like it has some more vendor-specific functionality for SQLite.

As a thumb rule for what you actuallycan use as parameters for queries, see the@param in the following statements:

SELECT col1, col1 FROM table1 WHERE col1 = @param1;INSERT INTO table1 (col1, col2) VALUES (@param1, @param2);UPDATE table1 SET col1 = @param1 WHERE col2 = @param2;DELETE FROM table1 WHERE col1 = @param1;

• 2
  \$\begingroup\$Well I added a comment before but I guess my internet was trashy and didn't post it. Thanks for your answer @PinCrash and I agree with what you said. I will be implementing my system based off yours and Malachi's answer so I thank you guys very much.\$\endgroup\$

  Mike Hunt

  – Mike Hunt

  2016-03-11 22:26:06 +00:00

  CommentedMar 11, 2016 at 22:26

• c#
• sql
• database
• sqlite