// Creates a new message based on data given in the request body. public async Task <HttpResponseMessage> CreateMessage(JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); // Verify if all parameters for the Message table exist, // return response code 400 if one or more of the parameters are missing. if (requestBodyData["type"] == null || requestBodyData["payload"] == null || requestBodyData["created"] == null || requestBodyData["lastModified"] == null || requestBodyData["senderID"] == null || requestBodyData["receiverID"] == null) { log.LogError($"Requestbody is missing data for the Message table!"); return(exceptionHandler.BadRequest(log)); } Message newMessage = requestBodyData.ToObject <Message>(); // All fields for the Message table are required. string queryString = $@"INSERT INTO [dbo].[Message] (type, payload, created, lastModified, senderID, receiverID)" + $"VALUES (@type, @payload, @created, @lastModified, @senderID, @receiverID);"; 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]. connection.Open(); try { // Insert new message into the Message table. using (SqlCommand command = new SqlCommand(queryString, connection)) { // Parameters are used to ensure no SQL injection can take place. dynamic dObject = newMessage; databaseFunctions.AddSqlInjection(requestBodyData, dObject, command); log.LogInformation($"Executing the following query: {queryString}"); await command.ExecuteNonQueryAsync(); } } 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} | Message created succesfully."); // Return response code [201 Created]. return(new HttpResponseMessage(HttpStatusCode.Created)); }
//Create a new connection between a tutorant and coach /* TODO: MAKE SURE THAT YOU CAN ONLY MAKE A CONNECTION WHEN THE STUDENTS EXISTS */ public async Task <HttpResponseMessage> CreateConnectionByTutorantID(int tutorantID, JObject tTocConnection) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); //Verify if all parameters for the CoachTutorantConnection exist. //One or more parameters may be missing, in which case a [400 Bad Request] is returned. if (tTocConnection["studentIDTutorant"] == null || tTocConnection["studentIDCoach"] == null || tTocConnection["status"] == null) { log.LogError("Requestbody is missing data for the CoachTutorantConnection table!"); return(exceptionHandler.BadRequest(log)); } /* Make a Connection entity from the requestBody after checking the required fields */ CoachTutorantConnection coachTutorantConnection = tTocConnection.ToObject <CoachTutorantConnection>(); string queryString = $@"INSERT INTO [dbo].[CoachTutorantConnection] (studentIDTutorant, studentIDCoach, status) VALUES (@studentIDTutorant, @studentIDCoach, @status);"; 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 { //Update the status for the tutorant/coach connection //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place dynamic dObject = coachTutorantConnection; databaseFunctions.AddSqlInjection(tTocConnection, dObject, command); log.LogInformation($"Executing the following query: {queryString}"); await command.ExecuteNonQueryAsync(); } } 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.Created} | Connection created succesfully."); //Return response code [201 Created]. return(new HttpResponseMessage(HttpStatusCode.Created)); }
//Delete the connections of a specific tutorant public async Task <HttpResponseMessage> DeleteConnectionByTutorantID(int tutorantID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); //Query string used to delete the coach from the coach table string queryString = $@"DELETE FROM [dbo].[CoachTutorantConnection] WHERE studentIDtutorant = @tutorantID"; 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 { //Delete the connection from a specific tutorant in the CoachTutorantConnection table //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place command.Parameters.Add("@tutorantID", SqlDbType.Int).Value = tutorantID; log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = await command.ExecuteNonQueryAsync(); //The studentIDs must be incorrect if no rows were affected, 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 deleted succesfully."); //Return response code [204 NoContent]. return(new HttpResponseMessage(HttpStatusCode.NoContent)); }
public async Task <HttpResponseMessage> DeleteMessageByID(int messageID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); string queryString = $@"DELETE FROM [dbo].[Message] 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)) { command.Parameters.Add("@MessageID", SqlDbType.DateTime).Value = messageID; 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 while deleting from the Tutorant table."); 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."); 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."); return(exceptionHandler.ServiceUnavailable(log)); } log.LogInformation($"{HttpStatusCode.NoContent} | Data deleted succesfully"); // Return response code [204 NoContent]. return(new HttpResponseMessage(HttpStatusCode.NoContent)); }
/* Updates the workload of the coach (in the coach table) */ public async Task <HttpResponseMessage> UpdateCoachByID(int coachID, JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); //newCoach.workload will be 0 if the requestbody contains no "workload" parameter, //in which case [400 Bad Request] is returned. if (requestBodyData["workload"] == null) { log.LogError("Requestbody contains no workload."); return(exceptionHandler.BadRequest(log)); } Coach newCoach = requestBodyData.ToObject <Coach>(); string queryString = $@"UPDATE [dbo].[Coach] SET workload = @workload WHERE studentID = @coachID;"; 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 { //Update the workload //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place /* PREVIOUSLY: */ //command.Parameters.Add("@workload", SqlDbType.Int).Value = newCoach.workload; //command.Parameters.Add("@coachID", SqlDbType.Int).Value = coachID; /* CHANGED to: due to consistency and scalability */ dynamic dObject = newCoach; databaseFunctions.AddSqlInjection(requestBodyData, dObject, command); log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = command.ExecuteNonQuery(); //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.ServiceUnavailable(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.BadRequest(log)); } log.LogInformation($"{HttpStatusCode.NoContent} | Data updated succesfully."); //Return response code [204 NoContent]. return(new HttpResponseMessage(HttpStatusCode.NoContent)); }
/* Returns the workload of the coach (from the coach table) */ public async Task <HttpResponseMessage> GetCoachByID(int coachID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); Coach newCoach = new Coach(); string queryString = $@"SELECT * FROM [dbo].[Coach] WHERE studentID = @coachID;"; 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 { //Get data from the Coach table by studentID using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place command.Parameters.Add("@coachID", SqlDbType.Int).Value = coachID; log.LogInformation($"Executing the following query: {queryString}"); //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlDataReader reader = await command.ExecuteReaderAsync()) { if (!reader.HasRows) { //Query was succesfully executed, but returned no data. //Return response code [404 Not Found] log.LogError("SQL Query was succesfully executed, but returned no data."); return(exceptionHandler.NotFound()); } while (reader.Read()) { newCoach = new Coach { studentID = SafeReader.SafeGetInt(reader, 1), workload = SafeReader.SafeGetInt(reader, 2) }; } } } } 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)); } var jsonToReturn = JsonConvert.SerializeObject(newCoach); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully."); //Return response code [200 OK] and the requested data. return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }
/* Returns the workload of the coach (from the coach table) */ /* Returns the profile of all coaches (from the student table) * and the workload of all coaches (from the coach table) */ public async Task <HttpResponseMessage> GetAllCoachProfiles() { ExceptionHandler exceptionHandler = new ExceptionHandler(log); List <CoachProfile> listOfCoachProfiles = new List <CoachProfile>(); string queryString = $@"SELECT Student.*, Coach.workload FROM [dbo].[Student] INNER JOIN [dbo].[Coach] ON Student.studentID = Coach.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 { /* Get all profiles from the Student and Coach tables */ using (SqlCommand command = new SqlCommand(queryString, connection)) { log.LogInformation($"Executing the following query: {queryString}"); //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlDataReader reader = await command.ExecuteReaderAsync()) { if (!reader.HasRows) { /*Query was succesfully executed, but returned no data. * Return response code [404 Not Found] */ log.LogError("SQL Query was succesfully executed, but returned no data."); return(exceptionHandler.NotFound()); } while (reader.Read()) { listOfCoachProfiles.Add(new CoachProfile( new Coach { studentID = SafeReader.SafeGetInt(reader, 0), workload = SafeReader.SafeGetInt(reader, 10) }, new Student { studentID = SafeReader.SafeGetInt(reader, 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) } )); } } } } 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)); } string jsonToReturn = JsonConvert.SerializeObject(listOfCoachProfiles); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully."); /* Return response code [200 OK] and the requested data. */ return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }
/* 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)); }
/* 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)); }
public async Task <HttpResponseMessage> GetMessageByID(int messageID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); Message newMessage = new Message(); string queryString = $@"SELECT * FROM [dbo].[Message] 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)) { command.Parameters.Add("@messageID", SqlDbType.Int).Value = messageID; log.LogInformation($"Executing the following query: {queryString}"); using (SqlDataReader reader = await command.ExecuteReaderAsync()) { if (!reader.HasRows) { //Query was succesfully executed, but returned no data. //Return response code [404 Not Found] log.LogError("SQL Query was succesfully executed, but returned no data."); return(exceptionHandler.NotFound()); } while (reader.Read()) { newMessage = new Message { MessageID = reader.GetInt32(0), type = SafeReader.SafeGetString(reader, 1), payload = SafeReader.SafeGetString(reader, 2), created = SafeReader.SafeGetDateTime(reader, 3), lastModified = SafeReader.SafeGetDateTime(reader, 4), senderID = SafeReader.SafeGetInt(reader, 5), receiverID = SafeReader.SafeGetInt(reader, 6) }; } } } } 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)); } var jsonToReturn = JsonConvert.SerializeObject(newMessage); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully."); //Return response code [200 OK] and the requested data. // Everything went fine, return status code 200. return(new HttpResponseMessage(HttpStatusCode.OK) { Content = new StringContent(jsonToReturn, Encoding.UTF8, "application/json") }); }
//Changes the status of a CoachTutorantConnection. public async Task <HttpResponseMessage> UpdateConnection(JObject requestBodyData) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); DatabaseFunctions databaseFunctions = new DatabaseFunctions(); //Verify if all parameters for the CoachTutorantConnection exist. //One or more parameters may be missing, in which case a [400 Bad Request] is returned. if (requestBodyData["status"] == null) { log.LogError("Requestbody is missing data for the CoachTutorantConnection table!"); return(exceptionHandler.BadRequest(log)); } /* Make a Connection entity from the requestBody after checking the required fields */ CoachTutorantConnection coachTutorantConnection = requestBodyData.ToObject <CoachTutorantConnection>(); string queryString = $@"UPDATE [dbo].[CoachTutorantConnection] SET status = @status WHERE studentIDTutorant = @studentIDTutorant AND studentIDCoach = @studentIDCoach;"; 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 { //Update the status for the tutorant/coach connection //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place dynamic dObject = coachTutorantConnection; databaseFunctions.AddSqlInjection(requestBodyData, dObject, command); log.LogInformation($"Executing the following query: {queryString}"); int affectedRows = await command.ExecuteNonQueryAsync(); //The studentIDs must be incorrect if no rows were affected, 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 profile of the tutorant (from the student table). public async Task <HttpResponseMessage> GetTutorantProfileByID(int tutorantID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); TutorantProfile newTutorantProfile = new TutorantProfile(); string queryString = $@"SELECT Student.* FROM [dbo].[Student] INNER JOIN [dbo].[Tutorant] ON Student.studentID = Tutorant.studentID WHERE Student.studentID = @tutorantID;"; 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. command.Parameters.Add("@tutorantID", SqlDbType.Int).Value = tutorantID; log.LogInformation($"Executing the following query: {queryString}"); //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlDataReader reader = await command.ExecuteReaderAsync()) { if (!reader.HasRows) { //Query was succesfully executed, but returned no data. //Return response code [404 Not Found] log.LogError("SQL Query was succesfully executed, but returned no data."); return(exceptionHandler.NotFound()); } while (reader.Read()) { newTutorantProfile = new TutorantProfile( new Tutorant { studentID = SafeReader.SafeGetInt(reader, 0) }, new Student { studentID = SafeReader.SafeGetInt(reader, 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) } ); } } } } 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)); } var jsonToReturn = JsonConvert.SerializeObject(newTutorantProfile); log.LogInformation($"{HttpStatusCode.OK} | Data shown succesfully"); //Return response code [200 OK] and the requested data. 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)); }
/* 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") }); }
/*Returns */ public async Task <HttpResponseMessage> Login() { ExceptionHandler exceptionHandler = new ExceptionHandler(log); UserAuth userAuth; JObject jObject; /* Read from the requestBody */ using (StringReader reader = new StringReader(await req.Content.ReadAsStringAsync())) { jObject = JsonConvert.DeserializeObject <JObject>(reader.ReadToEnd()); userAuth = jObject.ToObject <UserAuth>(); } /* Verify if all parameters for the Auth table exist. * One or more parameters may be missing, in which case a [400 Bad Request] is returned. */ if (jObject["studentID"] == null || jObject["password"] == null) { log.LogError("Requestbody is missing data for the Auth table!"); return(exceptionHandler.BadRequest(log)); } /* Create query for selecting data from the database */ string queryString = $@"SELECT part1, part2 FROM [dbo].[Auth] 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 { //Update the status for the tutorant/coach connection //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place command.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = userAuth.studentID; log.LogInformation($"Executing the following query: {queryString}"); using (SqlDataReader reader = command.ExecuteReader()) { /* If the student does not exist, it returns a notFoundException */ /* Return status code 404 */ while (reader.Read()) { //part1 = salt, part2 = hash userAuth.salt = reader.GetString(0); userAuth.hash = reader.GetString(1); } } } } 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.Created} | Connection created succesfully."); HttpResponseMessage response; if (userAuth.hash == encryptPassword(userAuth).hash) { //Return response code [201 Created]. response = new HttpResponseMessage(HttpStatusCode.OK); try { response.Content = new StringContent(leaseToken(userAuth.studentID.ToString())); } catch (Exception e) { log.LogError("Somthing went wrong within the token system"); log.LogError(e.Message); } return(response); } //Return response code [400 BadRequest]. response = new HttpResponseMessage(HttpStatusCode.BadRequest); log.LogInformation("test4"); return(response); }
/*Returns */ public async Task <HttpResponseMessage> CreateAuth() { ExceptionHandler exceptionHandler = new ExceptionHandler(log); UserAuth userAuth; JObject jObject; /* Read from the requestBody */ using (StringReader reader = new StringReader(await req.Content.ReadAsStringAsync())) { jObject = JsonConvert.DeserializeObject <JObject>(reader.ReadToEnd()); userAuth = jObject.ToObject <UserAuth>(); } /* Verify if all parameters for the Auth table exist. * One or more parameters may be missing, in which case a [400 Bad Request] is returned. */ if (jObject["studentID"] == null || jObject["password"] == null) { log.LogError("Requestbody is missing data for the Auth table!"); return(exceptionHandler.BadRequest(log)); } /* ******** To do ****************** * Check if the ID is already in the db * Check password length and stuff * Encrypt password (make function for) * ********************************* */ //encrypt password userAuth = encryptPassword(userAuth); /* Create query for setting the data into the database */ string queryString = $@"INSERT INTO [dbo].[Auth] (studentID, part1, part2) VALUES (@studentID, @salt, @hash);"; 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 { //Update the status for the tutorant/coach connection //The Query may fail, in which case a [400 Bad Request] is returned. using (SqlCommand command = new SqlCommand(queryString, connection)) { //Parameters are used to ensure no SQL injection can take place command.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = userAuth.studentID; command.Parameters.Add("@salt", System.Data.SqlDbType.VarChar).Value = userAuth.salt; command.Parameters.Add("@hash", System.Data.SqlDbType.VarChar).Value = userAuth.hash; log.LogInformation($"Executing the following query: {queryString}"); await command.ExecuteNonQueryAsync(); } } 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.Created} | Connection created succesfully."); //Return response code [201 Created]. HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK); try { response.Content = new StringContent(leaseToken(userAuth.studentID.ToString())); } catch (Exception e) { log.LogError("Somthing went wrong within the token system"); log.LogError(e.Message); } return(response); }
/* * Returns the data from a specific student (Coaches and Tutorants) * given by the studentID in the path. */ public async Task <HttpResponseMessage> GetStudentByID(int studentID) { ExceptionHandler exceptionHandler = new ExceptionHandler(log); Student newStudent = new Student(); /* Initialize the queryString */ string queryString = $"SELECT * FROM [dbo].[Student] 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)) { /* Adding SQL Injection to the StudentID parameter to prevent SQL attacks */ command.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = studentID; /* * Executing the queryString to get the student profile * and add the data of the student to a newStudent */ log.LogInformation($"Executing the following query: {queryString}"); using (SqlDataReader reader = await command.ExecuteReaderAsync()) { /* If the student does not exist, it returns a notFoundException */ /* Return status code 404 */ if (!reader.HasRows) { return(exceptionHandler.NotFound()); } while (reader.Read()) { newStudent = 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("Could not perform given query on the database"); 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)); } /* Convert the student to a JSON and Log a OK message */ var jsonToReturn = JsonConvert.SerializeObject(newStudent); 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") }); }