CODEWITHSUNDEEP
sqlite
user3026046
user3026046

Reputation: 1

SQLite REAL error

I am having some trouble with SQLite. I used to save "time" as an int, but i now want it as a double, which is causing some problems. I remake my database everytime i try a new fix, but it has no effect. Whenever i try to save, it get the error "4 values for 3 columns". when i change it all back to an int, it works fine again. Also, if i just remove "time all togeater from the saving process, i get the error "2 values for 3 columns". I have no clue what is going on. Does a real take up two values or whats the deal?

Creating the table:

            sql = "CREATE TABLE Speed (id INTEGER PRIMARY KEY AUTOINCREMENT, LanguageType VARCHAR(20), cardsetNumber INT, time REAL)";
        command = new SQLiteCommand(sql, dbConnection);
        command.ExecuteNonQuery();

Adding to the table:

    public void SaveSpeed(int cardsetNumber, LanguageType lt, double time)
    {
        Open();
        sql = "insert into Speed (cardsetNumber, LanguageType, time) values (" + cardsetNumber + ", '" + lt + "', " + time + ")";
        command = new SQLiteCommand(sql, dbConnection);
        command.ExecuteNonQuery();
        Close();
    }

Upvotes: 0

Views: 302

Answers (1)

O. R. Mapper
O. R. Mapper

Reputation: 20731

According to Wikipedia, Denmark uses a comma as a decimal separator. Most probably, the default culture of your process is the Danish one that imposes exactly that decimal separator when converting decimal numbers to strings.

You are doing two things that you shouldn't be doing in your code, though in this case, changing one will fix the problem:

  • You convert a value to a string based on a culture that is not the invariant culture for any other purpose than displaying it to the user.
  • You are constructing an SQL query with custom values by concatenating the values unescaped into the query string.

The first means that code that does not expect to read locale-specific number formats will simply interpret your string differently than intended. In particular, that is true when reading the string on another machine (that has another default locale set), but in this case, it is simply because the SQL parser does never expect any locale-specific number formats (and, as the comma serves as the argument separator, the syntax would actually stop being well-defined unless the parser would add any further locale-specific amendments).

The second can cause really bad trouble, so you are best served by always avoiding it.

So, what is happening here? With that locale-specific conversion, the time number is converted to something like 1,2, which is interpreted by the SQL parser as two integer numbers (separated with a comma), not as one decimal number. Hence the error message about the argument count.

The first problem could be solved by explicitly specifying the invariant culture for string conversions when the result is targeted at programs rather than humans. Always do that when you, or someone else, intends to read the value back into a number type, for example, when storing it in a text-based format for computers such as CSV or XML.

As for the second issue, to insert any custom (literal) values into your SQL query, (especially, but not exclusively, those that are not hard-coded somewhere but received from user-input), parametrize your query string:

sql = "insert into Speed (cardsetNumber, LanguageType, time) values (@cardsetNumber, @lt, @time)";
command = new SQLiteCommand(sql, dbConnection);
command.Parameters.AddWithValue("@cardsetNumber", cardsetNumber);
command.Parameters.AddWithValue("@lt", lt);
command.Parameters.AddWithValue("@time", time);

This will automatically insert any quotation or escape marks to the literal values, and there is no risk of intentional or accidental SQL injection.

Upvotes: 2

Related Questions