//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));
}
