Issues with POST and PUT in table with comma separated numeric values

Question

I am running into issues with POST and PUT for my backend. The relevant table structure can be seen here

DROP TABLE IF EXISTS coordinate;
CREATE TABLE coordinate
(
   id                    int(10) NOT NULL auto_increment,
   longitude             double(8,4) NOT NULL,
   latitude              double(8,4) NOT NULL,
   measurementId         int(10) default NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (measurementId) 
        REFERENCES measurement (id)
        ON DELETE CASCADE
);

It has a corresponding Model, which looks like this

public class Coordinate
{
    public Coordinate()
    {

    }

    public int ID { get; set; }

    public double Latitude { get; set; }

    public double Longitude { get; set; }

    public int MeasurementId { get; set; }

    ...
}

I am trying to insert numeric values into coordinate like so:

sql = "UPDATE coordinate SET longitude = '" + coordinate.Longitude + "," + " latitude = " + coordinate.Latitude + "," + " measurementId = " + coordinate.MeasurementId + "' WHERE id = " + id.ToString();

Notice the inclusion of the single bracket before the double bracket after longitude = '" and + "' WHERE ...

When I run this with a debugger the string sql is constructed like so:

sql = "UPDATE coordinate SET longitude = '55,6, latitude = 70,33, measurementId = 2' WHERE id = 2'

And my backend throws the following error:

{
 "Message": "An error has occurred.",
 "ExceptionMessage": "Data truncated for column 'longitude' at row 1",
 "ExceptionType": "MySql.Data.MySqlClient.MySqlException",
 ...
}

My hunch is that the sql string is not constructed properly because of the inclusion of the single bracket, so I remove it like and construct a new sql string like so:

sql = "UPDATE coordinate SET longitude = " + coordinate.Longitude + "," + " latitude = " + coordinate.Latitude + "," + " measurementId = " + coordinate.MeasurementId + " WHERE id = " + id.ToString();

Notice the removal of the single bracket ' in the places it was present before. Now, in the debugger, the string sql is constructed like so:

sql = "UPDATE coordinate SET longitude 55,6 latitude = 70,33, measurementId = 2 WHERE id = 2"

It's my impression that the numeric values should be separated by a . and not a ,, which could by why the backend now throws this error:

{
 "Message": "An error has occurred.",
 "ExceptionMessage": "You have an error in your SQL syntax; check the manual 
 that corresponds to your MySQL server version for the right syntax to use 
 near '6, latitude = 70,33, measurementId = 2 WHERE id = 2' at line 1",
}

I am a bit at a loss on how to then handle the insertion of numeric values in my database. Any help is much appreciated.

You need to rewrite this as a Parameterized Query to fix the SQL Injection vulnerability you have, then you will not need any string concatenation so will avoid unbalanced quotes, missing symbols etc. It will also fix several formatting issues. — Alex K., May 29 '19 at 10:42
[This (old) answer](https://stackoverflow.com/questions/6627239/insert-non-english-decimal-points-in-mysql/6627551#6627551) suggests that MySql doesn't support non-english decimal points in `INSERT` statements. Parameterised queries *might* solve that though, as @AlexK. suggested - definitely worth a try, as parameters in theory remove the ambiguity described in the linked answer (and that you're seeing here) — Diado, May 29 '19 at 11:29

score 2 · Accepted Answer · answered May 29 '19 at 18:36

As pointed out by in the comments to my question, using a parameterized query solved the issue with the added benefit of securing the SQL query. While DubDub's answer was an okay workaround, the following solves it in a more scalable, secure way.

The SQL query now looks like this:

 string sql = "UPDATE coordinate SET longitude = @Longitude, latitude = @Latitude, measurementId = @MeasurementId WHERE id = @ID";

 List<MySqlParameter> parameters = SqlFactory.CreateParametersFor(coordinate);

 cmd = SqlFactory.CreateParameterizedQuery(sql, parameters, conn);

 cmd.Parameters.Add(ID);

 await cmd.ExecuteNonQueryAsync();

Where the CreateParametersFor() and CreateParameterizedQuery() are implemented like so:

public static MySqlCommand CreateParameterizedQuery(string sql, List<MySqlParameter> parameters, MySqlConnection connection)
    {
        MySqlCommand cmd = new MySqlCommand(sql, connection);
        foreach (MySqlParameter parameter in parameters) cmd.Parameters.Add(parameter);
        return cmd;
    }

    public static List<MySqlParameter> CreateParametersFor(Coordinate coordinate)
    {
        List<MySqlParameter> parameters = new List<MySqlParameter>();

        MySqlParameter latitude = new MySqlParameter()
        {
            ParameterName = "@Latitude",
            Value = coordinate.Latitude
        };

        MySqlParameter longitude = new MySqlParameter()
        {
            ParameterName = "@Longitude",
            Value = coordinate.Longitude
        };

        MySqlParameter measurementId = new MySqlParameter()
        {
            ParameterName = "@MeasurementId",
            Value = coordinate.MeasurementId
        };

        parameters.Add(latitude);
        parameters.Add(longitude);
        parameters.Add(measurementId);

        return parameters;
    }

Boom, this looks much better in the end. I recommend changing the question title to include 'comma seperated decimal values' somewhere in there because that was the problem. Additionally, if you're interested in code efficiency/ security/ scalability, perhaps do some research into stored procedures, so you can separate your business layer from your data access layer. (it's not normally ideal having sql in the backend of pages for example, should be in their own classes or otherwise) — DubDub, May 30 '19 at 08:25
@DubDub I will look into that. I have updated the title of the question to reflect what the actual issue was. Thanks for your help :-) — kdhansen, May 30 '19 at 11:13

score 0 · Answer 2 · answered May 29 '19 at 10:42

0

Try the following.

sql = "UPDATE coordinate SET longitude = '" + coordinate.Longitude + "', latitude = '" + coordinate.Latitude + "', measurementId = '" + coordinate.MeasurementId + "' WHERE id = " + id.ToString();

answered May 29 '19 at 10:42

Shyam Vemula

591
2
14

@ShyamVemula No, that the data is truncated (the first error I highlighted in my question) – kdhansen May 29 '19 at 10:49
@ShyamVemula a data truncated error means the query executed, which it would not do if there was a syntax error. – Crowcoder May 29 '19 at 10:49
what are the `datatypes` of both longitude and latitude columns?. – Shyam Vemula May 29 '19 at 10:51
@ShyamVemula `double`, as stated in my question at the very top. – kdhansen May 29 '19 at 10:52
Those columns are `double` datatype and the data that u r passing is 55,6 and 70,33. `ALTER` the column datatypes to `char(20)` – Shyam Vemula May 29 '19 at 11:00
2

@ShyamVemula I am aware they are `double`, and no, I am *not* passing `55,6` - that conversion is happening somewhere in the chain. The data needs to be stored in the database as the proper numeric value it is and not a `char`. – kdhansen May 29 '19 at 11:02

DubDub · Answer 3 · 2019-05-29T11:45:54.817

Maybe give the following a go. I think the issue is that you need to use a '.' instead of a ',' to make decimal values clear to SQL. Bear in mind that when you try to return the longitude/latitude, it'll be returned as a string.

I haven't tested this but give it a try.

using System.Globalization;
public class Coordinate
{
    NumberFormatInfo nfi = new NumberFormatInfo();

    public Coordinate()
    {
        nfi.NumberDecimalSeparator = ".";
    }

    public int ID { get; set; }

    public double Latitude { get; set; }

    public double Longitude { get; set; }

    public int MeasurementId { get; set; }

    public string GetLatitudeComma()
    {
        return this.Latitude.ToString(nfi);
    }

    public string GetLongitudeComma()
    {
        return this.Longitude.ToString(nfi);
    }
}

And...

sql = "UPDATE coordinate SET longitude = '" + coordinate.GetLongitudeComma() + "," + " latitude = " + coordinate.GetLatitudeComma()+ "," + " measurementId = " + coordinate.MeasurementId + "' WHERE id = " + id.ToString();

Returning a string from properties typed as `double`? And also a stackoverflow condition. — Crowcoder, May 29 '19 at 11:06
Alright, made changes so it will actually compile, the issue is just the commas, remove them and you're golden. You could do it the other way with 'coordinate.Latitude.ToString().Replace(',','.')' as well, but can lead to messy code. — DubDub, May 29 '19 at 11:48

Goniometr · Answer 4 · 2019-05-29T11:56:38.880

0

replace "UPDATE coordinate SET longitude = '55,6, latitude = 70,33, measurementId = 2' WHERE id = 2' to "UPDATE coordinate SET longitude = '55.6', latitude = '70.33', measurementId = '2' WHERE id = '2'

replace "," to "." (55,6 to 55.6) sql not read and write number with ","

edited May 29 '19 at 11:56

answered May 29 '19 at 11:09

Goniometr

61
3

Issues with POST and PUT in table with comma separated numeric values

4 Answers4