/* Creates a new profile based on the data in the requestbody */ public async Task <HttpResponseMessage> CreateCoachProfile(JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); /* Split the requestBody in a Coach and Student entity */ JObject coachProfile = requestBodyData.SelectToken("coach").ToObject <JObject>(); JObject studentProfile = requestBodyData.SelectToken("student").ToObject <JObject>(); /* Check if the required data is present in the requestBody * before making the CoachProfile object */ if (coachProfile["studentID"] == null || coachProfile["workload"] == null || studentProfile["studentID"] == null) { log.LogError("Requestbody is missing the required data"); return(exceptionHandler.BadRequest(log)); } /* Create the entities from the split-requestBody fro readability */ Coach newCoach = coachProfile.ToObject <Coach>(); Student newStudent = studentProfile.ToObject <Student>(); /* Verify if the studentID of the "student" and the "coach" objects match. * A [400 Bad Request] is returned if these are mismatching. */ if (newStudent.studentID != newCoach.studentID) { log.LogError("RequestBody has mismatching studentID for student and coach objects!"); return(exceptionHandler.BadRequest(log)); } /* All fields for the Coach table are required */ string queryString_Coach = $@"INSERT INTO [dbo].[Coach] (studentID, workload) VALUES (@studentID, @workload);"; /* The SQL query for the Students table has to be dynamically generated, as it contains many optional fields. * By manually adding the columns to the query string (if they're present in the request body) we prevent * SQL injection and ensure no illegitimate columnnames are entered into the SQL query. */ /* Dynamically create the INSERT INTO line of the SQL statement: */ string queryString_Student = $@"INSERT INTO [dbo].[Student] ("; foreach (JProperty property in studentProfile.Properties()) { foreach (PropertyInfo props in newStudent.GetType().GetProperties()) { if (props.Name == property.Name) { queryString_Student += $"{property.Name}, "; } } } queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2); queryString_Student += ") "; /* Dynamically create the VALUES line of the SQL statement: */ queryString_Student += "VALUES ("; foreach (JProperty property in studentProfile.Properties()) { foreach (PropertyInfo props in newStudent.GetType().GetProperties()) { if (props.Name == property.Name) { queryString_Student += $"@{property.Name}, "; } } } queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2); queryString_Student += ");"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { /*The connection is automatically closed when going out of scope of the using block. * The connection may fail to open, in which case return a [503 Service Unavailable]. */ int studentCreated = 0; connection.Open(); try { /*Insert profile into the Student table. * The Query may fail, in which case a [400 Bad Request] is returned. */ using (SqlCommand command = new SqlCommand(queryString_Student, connection)) { /* Parameters are used to ensure no SQL injection can take place * To ensure generic code, a dynamic object is made to make a new Entity and be passed into the injection function */ dynamic dObject = newStudent; databaseFunctions.AddSqlInjection(studentProfile, dObject, command); log.LogInformation($"Executing the following query: {queryString_Student}"); // PREVIOUSLY: await command.ExecuteReaderAsync(); studentCreated = command.ExecuteNonQuery(); } /*Insert profile into the Coach table. * The Query may fail, in which case a [400 Bad Request] is returned. */ using (SqlCommand command = new SqlCommand(queryString_Coach, connection)) { /* Parameters are used to ensure no SQL injection can take place * To ensure generic code, a dynamic object is made to make a new Entity and be passed into the injection function*/ dynamic dObject = newCoach; databaseFunctions.AddSqlInjection(coachProfile, dObject, command); log.LogInformation($"Executing the following query: {queryString_Coach}"); /* Is the student query affected 0 rows (i.e.: Student did not create then * the coach cannot exists as well, so dont make the coach*/ if (studentCreated == 1) { // PREVIOUSLY: await command.ExecuteReaderAsync(); command.ExecuteNonQuery(); } else { log.LogError($"Cannot create coach profile, student does not exists"); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { /* The Query may fail, in which case a [400 Bad Request] is returned. * Reasons for this failure may include a PK violation (entering an already existing studentID). */ log.LogError("SQL Query has failed to execute."); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { /* The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ log.LogError("SQL connection has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"{HttpStatusCode.Created} | Profile created succesfully."); /* Return response code [201 Created]. */ return(new HttpResponseMessage(HttpStatusCode.Created)); }
public async Task <HttpResponseMessage> UpdateMessageByID(int messageID, JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); Message newMessage = requestBodyData.ToObject <Message>(); string queryString = $"UPDATE [dbo].[Message] SET "; /* Loop through the properties of the jObject Object which contains the values given in the requestBody * loop through the hardcoded properties in the Message Entity to check if they correspond with the requestBody * to prevent SQL injection. */ foreach (JProperty property in requestBodyData.Properties()) { foreach (PropertyInfo props in newMessage.GetType().GetProperties()) { if (props.Name == property.Name) { /* fill the queryString with the property names from the Message and their values */ queryString += $"{props.Name} = @{property.Name},"; } } } queryString = databaseFunctions.RemoveLastCharacters(queryString, 1); queryString += $@" WHERE MessageID = @messageID;"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { //The connection is automatically closed when going out of scope of the using block. //The connection may fail to open, in which case a [503 Service Unavailable] is returned. connection.Open(); try { using (SqlCommand command = new SqlCommand(queryString, connection)) { // Parameters are used to ensure no SQL injection can take place. /* pass the requestBody, the entity with the corresponding properties and the SqlCommand to the method * to ensure working SqlInjection for the incoming values*/ databaseFunctions.AddSqlInjection(requestBodyData, newMessage, command); log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = await command.ExecuteNonQueryAsync(); //The SQL query must have been incorrect if no rows were executed, return a [404 Not Found]. if (affectedRows == 0) { log.LogError("Zero rows were affected."); return(exceptionHandler.NotFound()); } } } catch (SqlException e) { //The Query may fail, in which case a [400 Bad Request] is returned. log.LogError("SQL Query has failed to execute."); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { //The connection may fail to open, in which case a [503 Service Unavailable] is returned. log.LogError("SQL has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"{HttpStatusCode.NoContent} | Data updated succesfully."); //Return response code [204 NoContent]. return(new HttpResponseMessage(HttpStatusCode.NoContent)); }
/* Returns the data from all the students that were created (Coaches and Tutorants) * based on the filters given by the user through query parameters. */ public async Task <HttpResponseMessage> GetAllStudents(List <string> parameters, List <string> propertyNames) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); List <Student> listOfStudents = new List <Student>(); string queryString = $"SELECT * FROM [dbo].[Student]"; /* If there are any query parameters, loop through the properties of the User * to check if they exist, if so, add the given property with its query value * to the queryString. This enables filtering between individual words in * the interests and study columns */ if (parameters.Count != 0 && parameters[0] != "") { queryString += $" WHERE"; for (int i = 0; i < parameters.Count; ++i) { if (parameters[i] == "interests" || parameters[i] == "study" || parameters[i] == "vooropleiding") { queryString += $" {propertyNames[i]} LIKE '%{parameters[i]}' AND"; } else { queryString += $" {propertyNames[i]} = '{parameters[i]}' AND"; } } //Remove ' AND' from the queryString to ensure this is the end of the filtering queryString = databaseFunctions.RemoveLastCharacters(queryString, 4); } else if (propertyNames.Count != 0 && parameters[0] == "") { queryString += $" ORDER BY"; for (int i = 0; i < parameters.Count; ++i) { queryString += $" {propertyNames[i]} AND"; } /* Remove ' AND' from the queryString to ensure this is the end of the filtering */ queryString = databaseFunctions.RemoveLastCharacters(queryString, 4); } try { using (SqlConnection connection = new SqlConnection(connectionString)) { /* The connection is automatically closed when going out of scope of the using block. * The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ connection.Open(); try { using (SqlCommand command = new SqlCommand(queryString, connection)) { log.LogInformation($"Executing the following query: {queryString}"); /* Executing the queryString to get all Student profiles * and add the data of all students to a list of students */ using (SqlDataReader reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { listOfStudents.Add(new Student { studentID = reader.GetInt32(0), firstName = SafeReader.SafeGetString(reader, 1), surName = SafeReader.SafeGetString(reader, 2), phoneNumber = SafeReader.SafeGetString(reader, 3), photo = SafeReader.SafeGetString(reader, 4), description = SafeReader.SafeGetString(reader, 5), degree = SafeReader.SafeGetString(reader, 6), study = SafeReader.SafeGetString(reader, 7), studyYear = SafeReader.SafeGetInt(reader, 8), interests = SafeReader.SafeGetString(reader, 9), vooropleiding = SafeReader.SafeGetString(reader, 10) }); } } } } catch (SqlException e) { /* The Query may fail, in which case a [400 Bad Request] is returned. */ log.LogError("SQL Query has failed to execute."); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { /* The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ log.LogError("SQL connection has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } /* Convert the list of students to a JSON and Log a OK message */ var jsonToReturn = JsonConvert.SerializeObject(listOfStudents); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully"); /* Return the JSON. Return status code 200 */ return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }
// Create a new profile based on the data in the request body. public async Task <HttpResponseMessage> CreateTutorantProfile(JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); JObject tutorantProfile = requestBodyData.SelectToken("tutorant").ToObject <JObject>(); JObject studentProfile = requestBodyData.SelectToken("student").ToObject <JObject>(); // Verify if all parameters for the tables exist. // One or more parameters may be missing, in which case a [400 Bad Request] is returned. if (tutorantProfile["studentID"] == null || studentProfile["studentID"] == null) { log.LogError("Requestbody is missing the required data"); return(exceptionHandler.BadRequest(log)); } Tutorant newTutorant = tutorantProfile.ToObject <Tutorant>(); Student newStudent = studentProfile.ToObject <Student>(); // Verify if the studentID of the "user" and the "tutorant" objects match. // A [400 Bad Request] is returned if these are mismatching. if (newTutorant.studentID != newStudent.studentID) { log.LogError("RequestBody has mismatching studentID for student and tutorant objects!"); return(exceptionHandler.BadRequest(log)); } // All fields for the Tutorant table are required. string queryStringTutorant = $@"INSERT INTO [dbo].[Tutorant] (studentID) VALUES (@studentID);"; // The SQL query for the Students table has to be dynamically generated, as it contains many optional fields. // By manually adding the columns to the query string (if they're present in the request body) we prevent // SQL injection and ensure no illegitimate columnnames are entered into the SQL query. // Dynamically create the INSERT INTO line of the SQL statement: string queryString_Student = $@"INSERT INTO [dbo].[Student] ("; foreach (JProperty property in studentProfile.Properties()) { foreach (PropertyInfo props in newStudent.GetType().GetProperties()) { if (props.Name == property.Name) { queryString_Student += $"{property.Name}, "; } } } queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2); queryString_Student += ") "; // Dynamically create the VALUES line of the SQL statement: queryString_Student += "VALUES ("; foreach (JProperty property in studentProfile.Properties()) { foreach (PropertyInfo props in newStudent.GetType().GetProperties()) { if (props.Name == property.Name) { queryString_Student += $"@{property.Name}, "; } } } queryString_Student = databaseFunctions.RemoveLastCharacters(queryString_Student, 2); queryString_Student += ");"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { // The connection is automatically closed when going out of scope of the using block. // The connection may fail to open, in which case return a [503 Service Unavailable]. int studentCreated = 0; connection.Open(); try { // Insert profile into the Student table using (SqlCommand command = new SqlCommand(queryString_Student, connection)) { // Parameters are used to ensure no SQL injection can take place. dynamic dObject = newStudent; databaseFunctions.AddSqlInjection(studentProfile, dObject, command); log.LogInformation($"Executing the following query: {queryString_Student}"); studentCreated = command.ExecuteNonQuery(); } // Insert profile into the Tutorant table. using (SqlCommand command = new SqlCommand(queryStringTutorant, connection)) { // Parameters are used to ensure no SQL injection can take place. dynamic dObject = newTutorant; databaseFunctions.AddSqlInjection(tutorantProfile, dObject, command); log.LogInformation($"Executing the following query: {queryStringTutorant}"); if (studentCreated == 1) { command.ExecuteNonQuery(); } else { log.LogError($"Cannot create tutorant profile, student does not exists"); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { // The Query may fail, in which case a [400 Bad Request] is returned. // Reasons for this failure may include a PK violation (entering an already existing studentID). log.LogError("SQL Query has failed to execute."); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { // The connection may fail to open, in which case a [503 Service Unavailable] is returned. log.LogError("SQL connection has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"{HttpStatusCode.Created} | Profile created succesfully."); // Return response code [201 Created]. return(new HttpResponseMessage(HttpStatusCode.Created)); }
/* Update the data from the student given by a requestBody */ public async Task <HttpResponseMessage> UpdateStudentByID(int studentID, JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); /* Read the requestBody and put the response into a jObject which can be read later * Also make a new user object and store the data in the user */ /* If the responseBody is empty (no data has been given by the user) * return a BadRequest to say that the User must fill the requestBody. * Bad request is status code 400 */ if (requestBodyData["studentID"] == null) { log.LogError($"Requestbody contains no studentID"); return(exceptionHandler.BadRequest(log)); } Student newStudent = requestBodyData.ToObject <Student>(); string queryString = $"UPDATE [dbo].[Student] SET "; /* Loop through the properties of the jObject Object which contains the values given in the requestBody * loop through the hardcoded properties in the Student Entity to check if they correspond with the requestBody * to prevent SQL injection. */ foreach (JProperty property in requestBodyData.Properties()) { foreach (PropertyInfo props in newStudent.GetType().GetProperties()) { if (props.Name == property.Name) { /* fill the queryString with the property names from the Message and their values */ queryString += $"{props.Name} = @{property.Name}, "; } } } /* Remove the last character from the queryString, which is ',' * And add the WHERE statement*/ queryString = databaseFunctions.RemoveLastCharacters(queryString, 2); queryString += $" WHERE studentID = @studentID;"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { /* * The connection is automatically closed when going out of scope of the using block. * The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ connection.Open(); try { using (SqlCommand command = new SqlCommand(queryString, connection)) { /* Parameters are used to prevent SQLInjection */ databaseFunctions.AddSqlInjection(requestBodyData, newStudent, command); log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = await command.ExecuteNonQueryAsync(); /* The SQL query must have been incorrect if no rows were executed, return a [404 Not Found] */ if (affectedRows == 0) { log.LogError("Zero rows were affected."); return(exceptionHandler.NotFound()); } } } catch (SqlException e) { /* The query may fail, in which case a [400 Bad Request] is returned. */ log.LogError("SQL Query has failed to execute."); log.LogError(e.Message); return(exceptionHandler.BadRequest(log)); } } } catch (SqlException e) { /* The connection may fail to open, in which case a [503 Service Unavailable] is returned. */ log.LogError("SQL connection has failed to open."); log.LogError(e.Message); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"Changed data of student: {studentID}"); //Return response code [204 NoContent]. return(new HttpResponseMessage(HttpStatusCode.NoContent)); }