2

I have methods for updating data in my SQLite database looking like this:

public void IncrementPoints(Phrase phrase, int pts)
{
    lock (l)
    {
        db2.Execute("UPDATE phrase SET Points = Points + " + pts +
                    " WHERE PhraseId = '" + phrase.PhraseId + "'");
    }
}

It was mentioned to me "why do you call lock in your code", but I am not sure of the answer.

Can someone tell me is it necessary to call lock like this when doing a simple update in SQLite?

Alan2
  • 23,493
  • 79
  • 256
  • 450
  • 1
    I would be more worried by all those string concatenations. A good way to give free access to your database data – Steve Dec 31 '18 at 12:23
  • Can you give an example of what might be a way to avoid the problem that you mention? Thanks – Alan2 Dec 31 '18 at 12:25
  • 1
    [Parameterized queries](https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i). There are millions of example on this site. Basically you don't put strings together to form an Sql Command, but put placeholders and add the values to the SqlLiteCommand.Parameters collection. This solves two issues: Parsing when there is a single quote in the input or you need to convert some decimal values to different cultures. Sql Injection [explained here](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) – Steve Dec 31 '18 at 12:30
  • 1
    Better to make clear what type `db2` is, which sqlite package you are using etc. The xamarin tags do not apply here. – H H Dec 31 '18 at 12:34
  • It's sqlite-net-pcl, thanks for the suggestions. I removed the Xamarin tags. – Alan2 Dec 31 '18 at 12:46
  • db2 is a SQLiteConnection object – Alan2 Dec 31 '18 at 12:48
  • 1
    But why do you have a single(ton) connection object? Create the connection when needed, makes your code much more resilient. When Sqlite does not offer a connectionpool you could creeate something yourself. Don't share a connection, certainly not across threads. – H H Dec 31 '18 at 14:57

0 Answers0