//TODO possible sql injection. SQL Query has interpolation generalPredicateFilter. generalPredicateFilter has interpolation SubstringForMatching then comes from url public IReadOnlyCollection <Event> GetAllFilteredEvents(Guid actorId, Guid trackerId, EventFilterData eventFilterData) { var generalPredicate = _mssqlFilter.CreateFilterMsSqlPredicates(eventFilterData, TableName); var generalPredicateFilter = generalPredicate != string.Empty ? $"and {generalPredicate}" : string.Empty; var events = _connection .Query <EventDto>( $@"select * from ItHappenedDB.Events as Events where Events.CreatorId = @ActorId and Events.TrackerId = @TrackerId {generalPredicateFilter}", new { SqlSchemaAndTableName = SchemaAndTableName, ActorId = actorId, TrackerId = trackerId //, //GeneralPredicateFilter = generalPredicateFilter // Can't pass generalPredicateFilter her. "Incorrect syntax near '@GeneralPredicate'.", }, _transaction ); return(_mapper.Map <Event[]>(events)); }
private static List <string> CreateStringPredicates(EventFilterData filterDataData, string tableName) { var stringFilterPredicates = new List <string>(); //date if (filterDataData.FromDateTime.HasValue) { stringFilterPredicates.Add( $"CAST({tableName}.HappensDate AS DATE) >= CAST('{GetMsSqlTimeString((DateTime) filterDataData.FromDateTime)}' AS DATETIME)"); } if (filterDataData.ToDateTime.HasValue) { stringFilterPredicates.Add( $"CAST({tableName}.HappensDate AS DATE) <= CAST('{GetMsSqlTimeString((DateTime) filterDataData.ToDateTime)}' AS DATETIME)"); } //scale if (filterDataData.ScaleLowerLimit.HasValue) { stringFilterPredicates.Add($"{tableName}.scale >= {filterDataData.ScaleLowerLimit}"); } if (filterDataData.ScaleUpperLimit.HasValue) { stringFilterPredicates.Add($"{tableName}.scale <= {filterDataData.ScaleUpperLimit}"); } //rating if (filterDataData.LowerLimitRating.HasValue) { stringFilterPredicates.Add($"{tableName}.rating >= {filterDataData.LowerLimitRating}"); } if (filterDataData.UpperLimitRating.HasValue) { stringFilterPredicates.Add($"{tableName}.rating <= {filterDataData.UpperLimitRating}"); } //comment (by substring) if (!string.IsNullOrEmpty(filterDataData.SubstringForMatching)) { stringFilterPredicates.Add( $"{tableName}.comment LIKE '%{filterDataData.SubstringForMatching}%'"); //possible sql injection? direct string import } //geotag if (filterDataData.GpsLatLeftDownCorner.HasValue) { stringFilterPredicates.Add($"{tableName}.latitudeGeo >= {filterDataData.GpsLatLeftDownCorner}"); } if (filterDataData.GpsLatRightUpperCorner.HasValue) { stringFilterPredicates.Add($"{tableName}.latitudeGeo <= {filterDataData.GpsLatRightUpperCorner}"); } if (filterDataData.GpsLngLeftDownCorner.HasValue) { stringFilterPredicates.Add($"{tableName}.longitudeGeo >= {filterDataData.GpsLngLeftDownCorner}"); } if (filterDataData.GpsLngRightUpperCorner.HasValue) { stringFilterPredicates.Add($"{tableName}.longitudeGeo <= {filterDataData.GpsLngRightUpperCorner}"); } return(stringFilterPredicates); }
public string CreateFilterMsSqlPredicates(EventFilterData filterDataData, string tableName) { var stringPredicates = CreateStringPredicates(filterDataData, tableName); return(stringPredicates.Count != 0 ? string.Join(AndConcat, stringPredicates) : string.Empty); }
public IReadOnlyCollection <Event> GetAllFilteredEvents(Guid userId, Guid trackerId, EventFilterData eventFilter) { return(_eventFiltrationRepository.GetAllFilteredEvents(userId, trackerId, eventFilter)); }