Checking the answer to a question in a user-entered set (C# API)-CodePudding

I need your help to check if the answer entered by a player on a game is right or wrong (data from a DB) Here is what I have already done for the moment in my db class:

public string VerifQuestions(int gameId, int trackId, int answerTypeId, string answer)
        {

            string result = "";
            _sqlConnection.Open(); //It opens my connection to my DB

            SqlCommand cmd = _sqlConnection.CreateCommand();

            SqlCommand cmd1 = _sqlConnection.CreateCommand(); //create my command =


            cmd.CommandText = "SELECT gameId, trackId, answerTypeId, answer FROM Song WHERE answer ="   answer; //request sql
            cmd1.CommandText = "SELECT Title FROM Track WHERE id ="   answerTypeId;

            //SqlDataReader reader = cmd.ExecuteReader();


            cmd.CommandText = (string)cmd.ExecuteScalar();
            cmd1.CommandText = (string)cmd1.ExecuteScalar();
            //executeScalar is a function to execute our query
                                                  //and returns the first column of the first row

            if (cmd1.CommandText == cmd.CommandText)
            {
                result = "true";
            }
            else
            {
                result = "false";
            }
            _sqlConnection.Close();
            return result;
        }
    }
}

My controller for my API :

        //This request will tell if the question entered is the correct answer
        //the user will have to enter the gameId, the trackId, the answer id, and the answer of the question
        // GET api/<ArticleArtistController>/5
        [HttpGet("gameId")]
        public string VerifQuestions(int gameId, int trackId, int answerTypeId, string answer)
        {
            return _db.VerifQuestions(gameId, trackId, answerTypeId, answer);
        }

The error I get System.Data.SqlClient.SqlException : 'Incorrect syntax near the keyword 'On'.'

CodePudding user response：

It seems that you have a problem in your created query to DB. The error is saying that you have a 'On' keyword in your query and it's not at the appropriate place so I suggest you make sure what is the exact query that you're sending to DB. preferably use some parameterized command. Good Luck .

CodePudding user response：

I would recommend using a parameterized SqlCommand, since the syntax error is likely coming from the answer or the answerTypeId.

cmd.CommandText = "SELECT gameId, trackId, answerTypeId, answer FROM Song WHERE answer = @answer"; //request sql
cmd.Parameters.Add("@answer", answer);
cmd1.CommandText = "SELECT Title FROM Track WHERE id = @answerTypeId";
cmd1.Parameters.Add("@answerTypeId", answerTypeId);

Note: This will also protect you from SQL injection attacks, which is a must in todays world.

The underlying problem is probably that your executed query looks something like this:

SELECT gameId, trackId, answerTypeId, answer FROM Song WHERE answer = Smoke on the water

Since the answer is not quoted, SQL will assume that on the water is a SQL command, rather than a part of the answer.