public EnrollmentStudentResponse GetEnrollmentByStudentIndexSqlInjectionVulnerable(string indexNumber) { var sqlQuery = "SELECT S.IndexNumber, E.Semester, E.StartDate, St.Name FROM Enrollment " + "E LEFT JOIN Student S on e.IdEnrollment = S.IdEnrollment " + "LEFT JOIN Studies St on E.IdStudy = St.IdStudy " + $"WHERE S.IndexNumber = {indexNumber}"; using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection, CommandText = sqlQuery }; connection.Open(); var dataReader = command.ExecuteReader(); if (dataReader.Read()) { return(new EnrollmentStudentResponse { IndexNumber = dataReader["IndexNumber"].ToString(), Semester = Parse(dataReader["Semester"].ToString()), StartDate = DateTime.Parse(dataReader["StartDate"].ToString()).ToString("yyyy-MM-dd"), StudiesName = dataReader["Name"].ToString() }); } throw new ResourceNotFoundException($"Enrollment for Student with indexNumber = {indexNumber} not found"); }
public TokenResponse RefreshJwtToken(RefreshTokenRequest refreshTokenRequest) { using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection }; connection.Open(); command.CommandText = "SELECT S.IndexNumber FROM Student S WHERE S.Refresh_Token = @RefreshToken"; command.Parameters.AddWithValue("RefreshToken", refreshTokenRequest.RefreshToken); var dataReader = command.ExecuteReader(); if (!dataReader.Read()) { throw new ResourceNotFoundException("Refresh token doesn't exist"); } var index = dataReader["IndexNumber"].ToString(); dataReader.Close(); var token = CreateJwtToken(index); var newRefreshToken = Convert.ToBase64String(Guid.NewGuid().ToByteArray()); command.Parameters.Clear(); command.CommandText = "UPDATE Student SET Refresh_Token = @RefreshToken WHERE IndexNumber = @IndexNumber"; command.Parameters.AddWithValue("@RefreshToken", newRefreshToken); command.Parameters.AddWithValue("IndexNumber", index); command.ExecuteNonQuery(); return(new TokenResponse { Token = new JwtSecurityTokenHandler().WriteToken(token), RefreshToken = newRefreshToken }); }
public Enrollment GetEnrollmentByStudentIndexSqlInjectionVulnerable(string indexNumber) { var sqlQuery = "SELECT S.IndexNumber, E.Semester, E.StartDate, St.Name FROM Enrollment " + "E LEFT JOIN Student S on e.IdEnrollment = S.IdEnrollment " + "LEFT JOIN Studies St on E.IdStudy = St.IdStudy " + $"WHERE S.IndexNumber = {indexNumber}"; using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection, CommandText = sqlQuery }; connection.Open(); var dataReader = command.ExecuteReader(); var enrollment = new Enrollment(); while (dataReader.Read()) { { enrollment.IndexNumber = dataReader["IndexNumber"].ToString(); enrollment.Semester = Parse(dataReader["Semester"].ToString()); enrollment.StartDate = DateTime.Parse(dataReader["StartDate"].ToString()).ToString("yyyy-MM-dd"); enrollment.StudiesName = dataReader["Name"].ToString(); } } return(enrollment); }
public EnrollmentResponse PromoteStudents(PromoteStudentsRequest promoteStudentsRequest) { using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection }; connection.Open(); command.CommandText = @"SELECT COUNT(1) FROM sys.objects WHERE name='PromoteStudents'"; if (!Convert.ToBoolean(Parse(command.ExecuteScalar().ToString()))) { var fileInfo = new FileInfo("Resources/promote_students_procedure.sql"); command.CommandText = fileInfo.OpenText().ReadToEnd(); command.ExecuteNonQuery(); } command.CommandText = "EXEC PromoteStudents @Semester, @Studies"; command.Parameters.AddWithValue("Semester", promoteStudentsRequest.Semester); command.Parameters.AddWithValue("Studies", promoteStudentsRequest.Studies); var dataReader = command.ExecuteReader(); if (dataReader.Read()) { return(new EnrollmentResponse { IdEnrollment = Parse(dataReader["IdEnrollment"].ToString()), Semester = Parse(dataReader["Semester"].ToString()), IdStudy = Parse(dataReader["IdStudy"].ToString()), StartDate = DateTime.Parse(dataReader["StartDate"].ToString()).ToString("yyyy-MM-dd") }); } throw new ResourceNotFoundException("Not Found"); }
public IEnumerable <Student> GetAllStudents() { const string sqlQuery = "SELECT S.FirstName, S.LastName, S.BirthDate, St.Name, E.Semester FROM Student S " + " LEFT JOIN Enrollment E ON S.IdEnrollment = E.IdEnrollment " + " LEFT JOIN Studies St ON E.IdStudy = St.IdStudy"; var students = new List <Student>(); using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection, CommandText = sqlQuery }; connection.Open(); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { var student = new Student { FirstName = dataReader["FirstName"].ToString(), LastName = dataReader["LastName"].ToString(), BirthDate = DateTime.Parse(dataReader["BirthDate"].ToString()).ToString("yyyy-MM-dd"), StudiesName = dataReader["Name"].ToString(), Semester = Parse(dataReader["Semester"].ToString()) }; students.Add(student); } return(students); }
public StudentWithStudiesResponse GetStudentByIndexNumberSqlInjectionInVulnerable(string indexNumber) { var sqlQuery = "SELECT S.FirstName, S.LastName, S.BirthDate, St.Name, E.Semester FROM Student S " + "LEFT JOIN Enrollment E ON S.IdEnrollment = E.IdEnrollment " + "LEFT JOIN Studies St ON E.IdStudy = St.IdStudy WHERE S.IndexNumber = @indexNumber"; using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection, CommandText = sqlQuery }; command.Parameters.AddWithValue("indexNumber", indexNumber); connection.Open(); var dataReader = command.ExecuteReader(); if (dataReader.Read()) { return(new StudentWithStudiesResponse { FirstName = dataReader["FirstName"].ToString(), LastName = dataReader["LastName"].ToString(), BirthDate = DateTime.Parse(dataReader["BirthDate"] .ToString()).ToString("yyyy-MM-dd"), StudiesName = dataReader["Name"].ToString(), Semester = Parse(dataReader["Semester"].ToString()) }); } throw new ResourceNotFoundException($"Student with indexNumber = {indexNumber} not found"); }
public bool CheckIfStudentExists(string index) { using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection }; connection.Open(); command.CommandText = "SELECT 1 FROM Student S WHERE S.IndexNumber = @IndexNumber"; command.Parameters.AddWithValue("IndexNumber", index); return(Convert.ToBoolean(Parse(command.ExecuteScalar().ToString()))); }
public TokenResponse LogIn(LoginRequestDto loginRequestDto) { using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection }; connection.Open(); var transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "SELECT S.Password, S.Salt FROM Student S WHERE S.IndexNumber = @IndexNumber"; command.Parameters.AddWithValue("IndexNumber", loginRequestDto.Index); var dataReader = command.ExecuteReader(); if (!dataReader.Read()) { throw new BadLoginOrPasswordException("Bad Login or Password"); } var salt = (byte[])dataReader["Salt"]; var storedPassword = dataReader["Password"].ToString(); dataReader.Close(); if (!PasswordUtils.ValidatePassword(loginRequestDto.Password, storedPassword, salt)) { throw new BadLoginOrPasswordException("Bad Login or Password"); } var token = CreateJwtToken(loginRequestDto.Index); var refreshToken = Convert.ToBase64String(Guid.NewGuid().ToByteArray()); command.Parameters.Clear(); command.CommandText = "UPDATE Student SET Refresh_Token = @RefreshToken WHERE IndexNumber = @IndexNumber"; command.Parameters.AddWithValue("@RefreshToken", refreshToken); command.Parameters.AddWithValue("IndexNumber", loginRequestDto.Index); command.ExecuteNonQuery(); transaction.Commit(); return(new TokenResponse { Token = new JwtSecurityTokenHandler().WriteToken(token), RefreshToken = refreshToken }); }
public EnrollmentResponse EnrollNewStudent(EnrollmentStudentRequest enrollmentStudentRequest) { using var connection = new SqlConnection(AppSettingsUtils.GetConnectionString()); using var command = new SqlCommand { Connection = connection }; connection.Open(); var transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = "SELECT s.IdStudy FROM Studies s WHERE s.Name = @StudiesName"; command.Parameters.AddWithValue("StudiesName", enrollmentStudentRequest.Studies); var dataReader = command.ExecuteReader(); if (!dataReader.Read()) { throw new ResourceNotFoundException( $"Studies by name {enrollmentStudentRequest.Studies} does not exist in database"); } var idStudy = Parse(dataReader["IdStudy"].ToString()); dataReader.Close(); command.Parameters.Clear(); command.CommandText = "SELECT * FROM Enrollment E WHERE E.Semester = 1 AND E.IdStudy = @IdStudy"; command.Parameters.AddWithValue("IdStudy", idStudy); dataReader = command.ExecuteReader(); var enrollmentResponse = new EnrollmentResponse(); if (!dataReader.Read()) { dataReader.Close(); command.Parameters.Clear(); command.CommandText = @"INSERT INTO Enrollment(IdEnrollment, Semester, StartDate, IdStudy) OUTPUT INSERTED.IdEnrollment, INSERTED.Semester, INSERTED.StartDate, INSERTED.IdStudy VALUES((SELECT MAX(E.IdEnrollment) FROM Enrollment E) + 1, @Semester, @StartDate, @IdStudy);"; command.Parameters.AddWithValue("Semester", 1); command.Parameters.AddWithValue("StartDate", DateTime.Now); command.Parameters.AddWithValue("IdStudy", idStudy); enrollmentResponse.IdEnrollment = Parse(command.ExecuteScalar().ToString()); enrollmentResponse.Semester = Parse(command.Parameters["Semester"].Value.ToString()); enrollmentResponse.IdStudy = Parse(command.Parameters["IdStudy"].Value.ToString()); enrollmentResponse.StartDate = DateTime.Parse(command.Parameters["StartDate"].Value.ToString()).ToString("yyyy-MM-dd"); } else { enrollmentResponse.IdEnrollment = Parse(dataReader["IdEnrollment"].ToString()); enrollmentResponse.Semester = Parse(dataReader["Semester"].ToString()); enrollmentResponse.IdStudy = Parse(dataReader["IdStudy"].ToString()); enrollmentResponse.StartDate = DateTime.Parse(dataReader["StartDate"].ToString()).ToString("yyyy-MM-dd"); } dataReader.Close(); command.Parameters.Clear(); command.CommandText = "SELECT S.IndexNumber FROM Student S WHERE IndexNumber = @indexNumber"; command.Parameters.AddWithValue("indexNumber", enrollmentStudentRequest.Index); dataReader = command.ExecuteReader(); if (dataReader.Read()) { throw new BadRequestException("Student Index number not unique");; } dataReader.Close(); command.Parameters.Clear(); var salt = PasswordUtils.GenerateSalt(); command.CommandText = @"INSERT INTO Student(IndexNumber, FirstName, LastName, BirthDate, IdEnrollment, Password, Salt) VALUES (@IndexNumber, @FirstName, @LastName, @BirthDate, @IdEnrollment, @Password, @Salt)"; command.Parameters.AddWithValue("IndexNumber", enrollmentStudentRequest.Index); command.Parameters.AddWithValue("FirstName", enrollmentStudentRequest.FirstName); command.Parameters.AddWithValue("LastName", enrollmentStudentRequest.LastName); command.Parameters.AddWithValue("BirthDate", enrollmentStudentRequest.BirthDate); command.Parameters.AddWithValue("IdEnrollment", enrollmentResponse.IdEnrollment); command.Parameters.AddWithValue("Password", PasswordUtils.CreateSaltedPasswordHash(enrollmentStudentRequest.Password, salt)); command.Parameters.AddWithValue("Salt", salt); command.ExecuteNonQuery(); transaction.Commit(); return(enrollmentResponse); }