Deserializing JSON and saving the object to MySQL database

Question

I am trying to save an object from POST request to my service. The request is sending a JSON:

{
"FirstName": "Ronnsie",
"LastName": "Wesleeyson",
"BirthPlace": "Zagreb",
"Gender": "M",
"OIB": "12345678901",
"CurrentPlace": "Mauritius",
"Department": "D_21510"
}

and the error I am getting is as follows:

{
"Message": "An error has occurred.",
"ExceptionMessage": "Error executing the command 'INSERT INTO Employee (FirstName, LastName, BirthPlace, CurrentPlace, Gender, Department, OIB) VALUES (, , , Mauritius, 1, 21510, )'. The error is '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 ' , , Mauritius, 1, 21510, )' at line 1'." ...}

Now the request hits the controller here:

    [HttpPost]
    [Route("")]
    public void Post([FromBody] Employee employee)
    {
        // saving id for the debugging purposes
        long id = persistence.SaveEmployee(employee);
    }

and the SaveEmployee function is as follows:

    public long SaveEmployee(Employee employee)
    {
        string sqlString = String.Format(
            "INSERT INTO Employee (FirstName, LastName, BirthPlace, CurrentPlace, Gender, Department, OIB) " + 
            "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})",
            employee.FirstName,
            employee.LastName,
            employee.BirthPlace,
            employee.CurrentPlace,
            employee.Gender == EmployeeGender.M ? 1 : 0,
            employee.Department.GetStringValue(),
            employee.OIB
        );

        MySqlCommand cmd = ExecuteSqlCommand(sqlString);
        return cmd.LastInsertedId;
    }

    MySqlCommand ExecuteSqlCommand(string sqlString)
    {
        try
        {
            // execute the SQL command
            MySqlCommand cmd = new MySqlCommand(sqlString, conn);
            cmd.ExecuteNonQuery();

            return cmd;
        }
        catch (MySqlException e)
        {
            // log the error
            throw new Exception(
                String.Format("Error executing the command '{0}'. The error is '{1}'.",
                             sqlString, e.Message));
        }
    }

I can't seem to find any error in the SQL syntax. However, it might be that there is a problem deserializing enums, so here they are:

DepartmentCode.cs

[JsonConverter(typeof(StringEnumConverter))] public enum DepartmentCode { D_21510, D_21520, D_21540, D_21570, D_SLFIN, D_SLKPO }

public static class DepartmentCodeExtensions
{
    const string S_21510 = "21510";
    const string S_21520 = "21520";
    const string S_21540 = "21540";
    const string S_21570 = "21570";
    const string S_SLFIN = "SLFIN";
    const string S_SLKPO = "SLKPO";

    public static string GetStringValue(this DepartmentCode dep)
    {
        switch (dep)
        {
            case DepartmentCode.D_21510:
                return S_21510;

            case DepartmentCode.D_21520:
                return S_21520;

            case DepartmentCode.D_21540:
                return S_21540;

            case DepartmentCode.D_21570:
                return S_21570;

            case DepartmentCode.D_SLFIN:
                return S_SLFIN;

            case DepartmentCode.D_SLKPO:
                return S_SLKPO;

            default:
                throw new ArgumentException("Given department code not supported.");
        }
    }

EmployeeGender.cs

[JsonConverter(typeof(StringEnumConverter))]
    public enum EmployeeGender
    {
        M,
        F
    }

Any ideas on what's the error here?

Adam · Accepted Answer

You have a SQL injection vulnerability. You should always parameterize your SQL and it is also the reason the query had a syntax error because you did not place the string parameters inside quotes. This is roughly what you want to do:

public long SaveEmployee(Employee employee)
    {
string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
MySqlConnection conn = new MySqlConnection(connStr);
        string sqlString = "INSERT INTO Employee (FirstName, LastName, BirthPlace, CurrentPlace, Gender, Department, OIB) " + 
            "VALUES (@FirstName,@LastName,@BirthPlace,@CurrentPlace,@Gender,@Department,@OIB)";

cmd.Parameters.AddWithValue(“@FirstName”, employee.FirstName);
cmd.Parameters.AddWithValue(“@LastName”, employee.LastName);
cmd.Parameters.AddWithValue(“@BirthPlace”, employee.BirthPlace);
cmd.Parameters.AddWithValue(“@CurrentPlace”, employee.CurrentPlace);
cmd.Parameters.AddWithValue(“@Gender”, employee.Gender == EmployeeGender.M ? 1 : 0);
cmd.Parameters.AddWithValue(“@Department”, employee.Department.GetStringValue());
cmd.Parameters.AddWithValue(“@OIB”, employee.OIB);

        MySqlCommand cmd = ExecuteSqlCommand(sqlString, conn);
        return cmd.LastInsertedId;
    }

Deserializing JSON and saving the object to MySQL database

Answers (2)

Related Questions