public void RunAnalysisVisitor(CommandResult commandResult)
{
var command = PluginContext.ProfilerData.Commands
.FirstOrDefault(x => x.CommandId == commandResult.CommandId &&
x.ApplicationIdentity.Equals(commandResult.ApplicationIdentity));
if (command == null)
{
return;
}
RunAnalysisVisitorOnCommand(() =>
{
var visitor = new UseFirstLevelCacheVisitor
{
Keys = getPrimaryKeys(commandResult)
};
var isUsingKeysInQuery = RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(command.Sql, command.SqlHash, visitor);
if (!isUsingKeysInQuery)
{
return(false);
}
return(isUsingFirstOrDefault(command));
}, command);
}
public void TestQueryWithTop1AndCompositeKeyComparision()
{
const string sql = @"SELECT TOP (1)
[Extent1].[MyProductId] AS [MyProductId],
[Extent1].[Name] AS [Name],
[Extent1].[Price] AS [Price],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[MyProductId] = @p__linq__0 and
[Extent1].[UserId] = 1";
var visitor = new UseFirstLevelCacheVisitor
{
Keys = new HashSet <string>
{
"MyProductId",
"UserId"
}
};
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckJoinsCount()
{
const string sql = @"
SELECT *
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name]
FROM
[dbo].[Items] AS [Extent1]
INNER JOIN [dbo].[CountryItem] AS [Extent2]
ON [Extent1].[Id] = [Extent2].[Items_Id]
INNER JOIN (
SELECT
[UnionAll1].[C1] AS [C1]
FROM (
SELECT 1 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable1]
UNION ALL
SELECT 2 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable2]
) AS [UnionAll1]
UNION ALL
SELECT 3 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable3]
) AS [UnionAll2] ON [Extent2].[Countries_Id] = [UnionAll2].[C1];
Select column_name(s)
from table1
cross join table2;";
var counterVisitor = new CounterVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(sql, sql.ComputeHash(), counterVisitor);
Assert.AreEqual(4, counterVisitor.CommandStatistics.JoinsCount);
}
public void CheckSelectsCounts()
{
const string sql = @"select * from tbl1; select * from tbl2;";
var counterVisitor = new CounterVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(sql, sql.ComputeHash(), counterVisitor);
Assert.AreEqual(2, counterVisitor.CommandStatistics.SelectsCount);
}
public void CheckSelectSumWithParenthesisIsSuspectedToArithmeticOverflow()
{
const string sql = @"select (sum((f1))) from tbl1;";
var visitor = new ArithmeticOverflowVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckUnparametrizedLikesWithFunction()
{
const string sql = @"select id from Products where Name like upper('P100%')";
var visitor = new UnparametrizedWhereClausesVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckFunctionInSelectStatementIsNotSuspected()
{
const string sql = @"select sum(f1) from tbl1";
var visitor = new FunctionCallsInWhereClauseVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
public void CheckHasUpperFunction()
{
const string sql = @"select sum(f1) from tbl1 where upper(f2)='TEST'";
var visitor = new FunctionCallsInWhereClauseVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void TestHasSelectStarExpression()
{
const string sql = @"SELECT * from tbl1 where f1 = 12";
var visitor = new SelectStarExpressionVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckComparisonWithNullLiteralWithExtraParenthesis()
{
var sql = @"select * from tbl1 where
(((data)=('test')) and ((name)=(null)))";
var visitor = new NullComparisonVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckComparisonWithNullLiteral()
{
var sql = @"select * from tbl1 where
data='test' and name=null";
var visitor = new NullComparisonVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckFunctionAsValueIsNotSuspected()
{
const string sql = @"SELECT [Id] FROM [dbo].[Products] WHERE
@@ROWCOUNT > 0 AND [Id] = scope_identity()";
var visitor = new FunctionCallsInWhereClauseVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
public void CheckQueryHasTopKeyword()
{
const string sql = @"SELECT Top 3 BusinessEntityID, FirstName, LastName
FROM Testoffset";
var visitor = new UnboundedResultSetVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
public void CheckQueryWithoutWhereClauses()
{
const string sql = @"SELECT BusinessEntityID, FirstName, LastName
FROM Testoffset";
var visitor = new UnboundedResultSetVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckDeletesCount()
{
const string sql = @"
DELETE [dbo].[Products] WHERE ([Id] = @0);
SELECT [Id] FROM [dbo].[Categories];
DELETE [dbo].[Products] WHERE ([Id] = @0);";
var counterVisitor = new CounterVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(sql, sql.ComputeHash(), counterVisitor);
Assert.AreEqual(2, counterVisitor.CommandStatistics.DeletesCount);
}
public void CheckSelectCountIsNotSuspectedToArithmeticOverflow()
{
const string sql = @"SELECT [GroupBy1].[A1] AS [C1]
FROM (SELECT COUNT(1) AS [A1]
FROM [dbo].[__MigrationHistory] AS [Extent1]
WHERE [Extent1].[ContextKey] = @p__linq__0) AS [GroupBy1];";
var visitor = new ArithmeticOverflowVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
public void CheckUpdatesCount()
{
const string sql = @"
UPDATE [Users] SET [LastVisitDateTime] = @0 WHERE ([Id] = @1);
SELECT [Id] FROM [dbo].[Categories];
UPDATE [Users] SET [LastVisitDateTime] = @0 WHERE ([Id] = @1);";
var counterVisitor = new CounterVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(sql, sql.ComputeHash(), counterVisitor);
Assert.AreEqual(2, counterVisitor.CommandStatistics.UpdatesCount);
}
public void CheckQueryHasOffsetFetch()
{
const string sql = @"SELECT BusinessEntityID, FirstName, LastName
FROM Testoffset
ORDER BY BusinessEntityID
OFFSET 3 ROWS
FETCH First 3 ROWS only";
var visitor = new UnboundedResultSetVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
public void CheckSelectSumCastAsBigIntIsNotSuspectedToArithmeticOverflow()
{
const string sql = @"SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
SUM( CAST( [Extent1].[Amount] AS bigint)) AS [A1]
FROM [dbo].[Transactions] AS [Extent1]
) AS [GroupBy1]";
var visitor = new ArithmeticOverflowVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
public void CheckLikesCount()
{
const string sql = @"
SELECT * FROM Person.Contact
WHERE LastName LIKE '%mith';
SELECT [Id] FROM [dbo].[Categories];
SELECT * FROM Person.Contact
WHERE LastName LIKE '%' + @blah + '%';";
var counterVisitor = new CounterVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(sql, sql.ComputeHash(), counterVisitor);
Assert.AreEqual(2, counterVisitor.CommandStatistics.LikesCount);
}
public void CheckComparisonWithIntLiteralWithParenthesis()
{
const string sql = @"SELECT TOP (20)
[Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName]
FROM
(SELECT [Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName],
row_number() OVER (ORDER BY [Extent1].[FirstName] ASC) AS [row_number]
FROM [dbo].[Customers] AS [Extent1]) AS [Extent1]
WHERE (([Extent1].[row_number]) > (10)) ORDER BY [Extent1].[FirstName] ASC";
var visitor = new UnparametrizedWhereClausesVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckInsertsCount()
{
const string sql = @"
INSERT [dbo].[Categories]([Name], [Title])
VALUES (@0, @1);
SELECT [Id] FROM [dbo].[Categories]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity();
INSERT [dbo].[Categories]([Name], [Title])
VALUES (@0, @1);
SELECT [Id] FROM [dbo].[Categories]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity();";
var counterVisitor = new CounterVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(sql, sql.ComputeHash(), counterVisitor);
Assert.AreEqual(2, counterVisitor.CommandStatistics.InsertsCount);
}
public void CheckComparisonNullWithNullVariable()
{
var sql = @"SELECT [Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Title] AS [Title]
FROM [dbo].[Categories] AS [Extent1]
WHERE ([Extent1].[Title] = @p__linq__0) -- this shouldn't be here
OR (([Extent1].[Title] IS NULL)
AND (@p__linq__0 IS NULL));";
var visitor = new NullComparisonVisitor();
visitor.NullVariableNames.Add("@p__linq__0");
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckHasUpperFunctionWithParenthesis()
{
const string sql = @"SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Price] AS [Price],
[Extent1].[CategoryId] AS [CategoryId]
FROM [dbo].[Products] AS [Extent1]
WHERE ((UPPER([Extent1].[Name])) = (UPPER('Test'))) OR
((UPPER([Extent1].[Name]) IS NULL) AND
(UPPER('Test') IS NULL))";
var visitor = new FunctionCallsInWhereClauseVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void CheckQueryHasParametrizedSkipAndTake()
{
const string sql = @"SELECT
[Extent1].[MyProductId] AS [MyProductId],
[Extent1].[Name] AS [Name],
[Extent1].[Price] AS [Price],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[MyProducts] AS [Extent1]
ORDER BY [Extent1].[MyProductId] ASC
OFFSET @p__linq__0 ROWS FETCH NEXT @p__linq__1 ROWS ONLY";
var visitor = new UnParametrizedSkipAndTakeVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsFalse(visitor.IsSuspected);
}
private void Commands_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
{
switch (e.Action)
{
case NotifyCollectionChangedAction.Add:
foreach (Command command in e.NewItems)
{
var localCommand = command;
_callbacksManager.RunAnalysisVisitorOnCommand(() =>
{
var aliasFinder = new AliasResolutionVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitor(localCommand.Sql, localCommand.SqlHash, aliasFinder);
var visitor = new FindEqualityJoinVisitor(aliasFinder.Aliases);
return(RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(localCommand.Sql, localCommand.SqlHash, visitor));
}, command);
}
break;
}
}
public void SameQueriesWithDifferentLiteralValuesShouldHaveTheSameHash()
{
var sql1 = @"SELECT TOP (2)
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Value] AS [Value]
FROM [dbo].[SiteOptions] AS [Extent1]
WHERE (N'Store1' = [Extent1].[Name])";
var sql2 = @"SELECT TOP (2)
[Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name],
[Extent1].[Value] AS [Value]
FROM [dbo].[SiteOptions] AS [Extent1]
WHERE (N'Store2' = [Extent1].[Name])";
var hash1 = RunTSqlFragmentVisitor.GetNormalizedSqlHash(sql1, sql1.ComputeHash());
var hash2 = RunTSqlFragmentVisitor.GetNormalizedSqlHash(sql2, sql2.ComputeHash());
Assert.AreEqual(hash1.NormalizedSqlHash, hash2.NormalizedSqlHash);
}
public void CheckComparisonWithStringLiteral()
{
const string sql = @"SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [Roles] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Role_Id] AS [Role_Id], [Extent2].[User_Id] AS [User_Id], [Extent3].[Id] AS [Id], [Extent3].[FriendlyName] AS [FriendlyName], [Extent3].[UserName] AS [UserName], [Extent3].[Password] AS [Password], [Extent3].[EMail] AS [EMail], [Extent3].[IsActive] AS [IsActive], [Extent3].[ReceiveDailyEmails] AS [ReceiveDailyEmails], [Extent3].[EmailIsValidated] AS [EmailIsValidated], [Extent3].[RegistrationCode] AS [RegistrationCode], [Extent3].[NumberOfPosts] AS [NumberOfPosts], [Extent3].[NumberOfComments] AS [NumberOfComments], [Extent3].[NumberOfLinks] AS [NumberOfLinks], [Extent3].[NumberOfProjects] AS [NumberOfProjects], [Extent3].[NumberOfDrafts] AS [NumberOfDrafts], [Extent3].[NumberOfProjectsFeedbacks] AS [NumberOfProjectsFeedbacks], [Extent3].[NumberOfProjectsComments] AS [NumberOfProjectsComments], [Extent3].[NumberOfLinksComments] AS [NumberOfLinksComments], [Extent3].[NumberOfSurveys] AS [NumberOfSurveys], [Extent3].[NumberOfVoteComments] AS [NumberOfVoteComments], [Extent3].[NumberOfAdvertisements] AS [NumberOfAdvertisements], [Extent3].[NumberOfAdvertisementComments] AS [NumberOfAdvertisementComments], [Extent3].[NumberOfCourses] AS [NumberOfCourses], [Extent3].[NumberOfLearningPaths] AS [NumberOfLearningPaths], [Extent3].[LastVisitDateTime] AS [LastVisitDateTime], [Extent3].[IsRestricted] AS [IsRestricted], [Extent3].[HomePageUrl] AS [HomePageUrl], [Extent3].[Photo] AS [Photo], [Extent3].[Description] AS [Description], [Extent3].[DateOfBirth] AS [DateOfBirth], [Extent3].[Location] AS [Location], [Extent3].[IsJobsSeeker] AS [IsJobsSeeker], [Extent3].[IsEmailPublic] AS [IsEmailPublic], [Extent3].[FacebookName] AS [FacebookName], [Extent3].[TwitterName] AS [TwitterName], [Extent3].[LinkedInProfileId] AS [LinkedInProfileId], [Extent3].[GooglePlusProfileId] AS [GooglePlusProfileId], [Extent3].[StackOverflowId] AS [StackOverflowId], [Extent3].[GithubId] AS [GithubId], [Extent3].[NugetId] AS [NugetId], [Extent3].[CodePlexId] AS [CodePlexId], [Extent3].[CodeProjectId] AS [CodeProjectId], [Extent3].[SourceforgeId] AS [SourceforgeId], [Extent3].[Rating_TotalRating] AS [Rating_TotalRating], [Extent3].[Rating_TotalRaters] AS [Rating_TotalRaters], [Extent3].[Rating_AverageRating] AS [Rating_AverageRating], [Extent3].[CreatedOn] AS [CreatedOn], [Extent3].[CreatedOnPersian] AS [CreatedOnPersian], [Extent3].[CreatedBy] AS [CreatedBy], [Extent3].[CreatedByIp] AS [CreatedByIp], [Extent3].[CreatedByBrowserName] AS [CreatedByBrowserName], [Extent3].[ModifiedOn] AS [ModifiedOn], [Extent3].[ModifiedOnPersian] AS [ModifiedOnPersian], [Extent3].[ModifiedBy] AS [ModifiedBy], [Extent3].[ModifiedByIp] AS [ModifiedByIp], [Extent3].[ModifiedByBrowserName] AS [ModifiedByBrowserName]
FROM [RoleUsers] AS [Extent2]
INNER JOIN [Users] AS [Extent3] ON [Extent3].[Id] = [Extent2].[User_Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[Role_Id]
WHERE (N'Admin' = [Extent1].[Name]) AND ([Join1].[IsActive] = @p__linq__0)
) AS [GroupBy1]";
var visitor = new UnparametrizedWhereClausesVisitor();
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
public void SameQueriesWithDifferentParameterNamesShouldHaveTheSameHash()
{
var sql1 = @"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[UserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @p__linq__0";
var sql2 = @"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[ClaimType] AS [ClaimType],
[Extent1].[ClaimValue] AS [ClaimValue]
FROM [dbo].[UserClaims] AS [Extent1]
WHERE [Extent1].[UserId] = @EntityKeyValue";
var hash1 = RunTSqlFragmentVisitor.GetNormalizedSqlHash(sql1, sql1.ComputeHash());
var hash2 = RunTSqlFragmentVisitor.GetNormalizedSqlHash(sql2, sql2.ComputeHash());
Assert.AreEqual(hash1.NormalizedSqlHash, hash2.NormalizedSqlHash);
}
public void TestQueryWithTop1AndKeyComparisionWithoutAlias()
{
const string sql = @"SELECT TOP (1)
[MyProductId],
[Name],
[Price],
[CategoryId],
[UserId]
FROM [Products]
WHERE [MyProductId] = @p__linq__0";
var visitor = new UseFirstLevelCacheVisitor
{
Keys = new HashSet <string>
{
"MyProductId"
}
};
RunTSqlFragmentVisitor.AnalyzeFragmentVisitorBase(sql, sql.ComputeHash(), visitor);
Assert.IsTrue(visitor.IsSuspected);
}
