public List <MovieListData> GetMovieList(string SearchText) { List <MovieListData> movieCollection = new List <MovieListData>(); SqlConnection sqlconnection = sqlsetup.SqlConnectionSetup(); try { SearchText.Trim(); String search = "\'%" + SearchText + "%\'"; sqlconnection.Open(); SqlCommand sqlTableFetch = new SqlCommand("SELECT Name,movie_id from IMDB.dbo.Movie Where Name like " + search); sqlTableFetch.Connection = sqlconnection; SqlDataReader reader = sqlTableFetch.ExecuteReader(); while (reader.Read()) { MovieListData movielist = new MovieListData(); movielist.Name = reader["Name"].ToString(); movielist.movie_id = Guid.Parse(reader["movie_id"].ToString()); movieCollection.Add(movielist); } reader.Close(); } catch (Exception e) { throw e; } finally { sqlconnection.Close(); } return(movieCollection); }
public IActionResult GetMovies(string searchParam, string column, int pageNumber, int itemsPerPage, string sortKey, string sortOrder) { if (searchParam == null || column == null || pageNumber == 0 || itemsPerPage == 0 || sortKey == null || sortOrder == null) { return(BadRequest()); } //sanitizing Inputs char[] Sanitizer = searchParam.ToCharArray(); Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c) || char.IsWhiteSpace(c)); searchParam = new string(Sanitizer); Sanitizer = column.ToCharArray(); Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c)); column = new string(Sanitizer); Sanitizer = sortKey.ToCharArray(); Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c)); sortKey = new string(Sanitizer); Sanitizer = sortOrder.ToCharArray(); Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c)); sortOrder = new string(Sanitizer); //alternative to Sanitizing, but apparently slower? /* * Regex sanitizer = new Regex("[A-Za-z0-9]"); * searchParam = sanitizer.Replace(searchParam, ""); * column = sanitizer.Replace(column, ""); * sortKey = sanitizer.Replace(sortKey, ""); * sortOrder = sortOrder.Replace(sortOrder, ""); */ #pragma warning disable EF1000 // Possible SQL injection vulnerability. var result = database.Movie.FromSql("" + "SELECT * FROM Movie " + "WHERE " + column + " " + "LIKE '%" + searchParam + "%' " + "ORDER BY " + sortKey + " " + sortOrder) #pragma warning restore EF1000 // Possible SQL injection vulnerability. .ToList(); int hits = result.Count; result = result.Skip((pageNumber - 1) * itemsPerPage) .Take(itemsPerPage) .ToList(); MovieListData returnValue = new MovieListData(result, hits); return(Ok(returnValue)); }