internal static void Run(SyntaxNodeAnalysisContext context, InterpolatedStringExpressionSyntax token)
{ //Check string
string id = token.ToFullString();
if (string.IsNullOrWhiteSpace(id))
{
return;
}
//Send to class Helper
string sql = Helper.BuildSqlStringFromIdString(context, id);
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
//Send to Class Parser
List <string> errors = SqlParser.Parse(sql);
if (errors.Count == 0)
{
return;
}
//Report Error
string errorText = String.Join("\r\n", errors);
var diagnostic = Diagnostic.Create(Rule, context.Node.GetLocation(), errorText);
context.ReportDiagnostic(diagnostic);
}
public void BigHonkinSubquery()
{
string sql = @"SELECT SUM((CASE
WHEN a.type = 1 THEN 'a'
ELSE 'b'
END)) AS [UnreadCount], COUNT(*) AS [TotalCount]
FROM (
SELECT 1 AS [value], [t0].[field1], [t1].[field2], [t1].[field3]
FROM [dbo].[table1] AS [t0]
LEFT OUTER JOIN ([dbo].[table2] AS [t1]
INNER JOIN ([dbo].[table3] AS [t2]
INNER JOIN ([dbo].[table4] AS [t3]
INNER JOIN [dbo].[table5] AS [t4] ON [t4].[ID] = [t3].[OtherID]) ON [t3].[ID] = [t2].[OtherID]) ON [t2].[ID] = [t1].[OtherID]
)) AS [t9]
WHERE ([t9].[OtherID] IS NULL)
GROUP BY [t9].[value]";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(2, parsedSql.Select.Fields.Count);
Assert.Equal("[unreadcount]", parsedSql.Select.Fields[0].Alias);
Assert.Equal("[totalcount]", parsedSql.Select.Fields[1].Alias);
Assert.Equal(1, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(1, parsedSql.Select.Wheres.Count);
Assert.Equal(1, parsedSql.Select.GroupBys.Count);
}
internal static void Run(SyntaxNodeAnalysisContext context, BinaryExpressionSyntax token)
{
string id = token.ToFullString();
if (string.IsNullOrWhiteSpace(id))
{
return;
}
if (id.Contains("+") == false)
{
return;
}
string[] list = id.Split('+');
string sql = BuildSqlStringFromList(list, context, id);
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
List <string> errors = SqlParser.Parse(sql);
if (errors.Count == 0)
{
return;
}
string errorText = String.Join("\r\n", errors);
var diagnostic = Diagnostic.Create(RuleParam1, context.Node.GetLocation(), errorText);
context.ReportDiagnostic(diagnostic);
}
internal static void Run(SyntaxNodeAnalysisContext context, LiteralExpressionSyntax literalExpression)
{
if (literalExpression == null)
{
return;
}
if (literalExpression.IsKind(SyntaxKind.StringLiteralExpression) &&
literalExpression.Token.IsKind(SyntaxKind.StringLiteralToken))
{
var sql = literalExpression.Token.ValueText;
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
List <string> errors = SqlParser.Parse(sql);
if (errors.Count == 0)
{
return;
}
string errorText = String.Join("\r\n", errors);
var diagnostic = Diagnostic.Create(Rule, literalExpression.GetLocation(), errorText);
context.ReportDiagnostic(diagnostic);
}
}
internal static void Run(SyntaxNodeAnalysisContext context, BinaryExpressionSyntax token)
{ //Check binaryexpression case
string id = token.ToFullString();
if (string.IsNullOrWhiteSpace(id))
{
return;
}
if (id.Contains("+") == false)
{
return;
}
//Split string
string[] list = id.Split('+');
string sql = BuildSqlStringFromList(list, context, id);
try
{
AzureML(sql);
sendData(getJson);
while (getResult == "")
{
}
Deserialize(getResult);
getResult = "";
} catch { }
//Report warning union query injection
if (reportWarningUnionquery != "0")
{
Diagnostics.UnionQueryBinary.Run(context);
}
//Report warning illegal query injection
if (reportWarningIllegalquery != "0")
{
Diagnostics.IllegalBinary.Run(context);
}
//Report warning piggybacked query injection
if (reportWarningPiggybackedquery != "0")
{
Diagnostics.PiggybackedBinary.Run(context);
}
//Check string
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
//Check Error
List <string> errors = SqlParser.Parse(sql);
if (errors.Count == 0)
{
return;
}
//Report Error
string errorText = String.Join("\r\n", errors);
var diagnostic = Diagnostic.Create(Rule, context.Node.GetLocation(), errorText);
context.ReportDiagnostic(diagnostic);
}
/// <summary>
/// ExecutePaginated sql file most simple
/// </summary>
static void ExecuteSimple(IExecutor executor)
{
// create parameter object instance for 2-way-sql
var condition = new SqlCondition
{
MiddleNames = new List <string> {
"A", "J", "M"
}
};
var watch = new Stopwatch();
watch.Start();
// create SqlParse instance
var parser = new SqlParser(FilePath, condition);
// parse sql file
var result = parser.Parse();
watch.Stop();
Console.WriteLine($"time\t{watch.Elapsed}");
watch.Reset();
watch.Start();
Debug.WriteLine($"+++>{result.DebugSql}");
executor.Execute(ConnectionString, result);
watch.Stop();
Console.WriteLine($"time\t{watch.Elapsed}");
}
public void BiggerCaseAndGroupBy()
{
string sql = @"SELECT SUM(
(CASE
WHEN [t9].[IsUnviewed] = 1 THEN 14
ELSE 15
END)) AS [UnreadCount], COUNT(*) AS [TotalCount]
FROM (
SELECT 1 AS [value], [t0].[MailboxFolderID], [t1].[PItemStatusEnumID], [t1].[CurrentOpTypeEnumID], [t1].[CurrentOpExtractStatusEnumID], [t1].[ID], [t0].[SystemFolderEnumID], [t1].[PurgeScansStatusEnumID], [t1].[IsScanOnly], [t0].[MailboxID], [t0].[IsUnviewed]
FROM [dbo].[VItems] AS [t0]
LEFT OUTER JOIN ([dbo].[PItems] AS [t1]
INNER JOIN ([dbo].[PItemLocations] AS [t2]
INNER JOIN ([dbo].[Locations] AS [t3]
INNER JOIN [dbo].[Facilities] AS [t4] ON [t4].[ID] = [t3].[FacilityID]) ON [t3].[LocationID] = [t2].[LocationID]) ON [t2].[ID] = [t1].[PItemLocationID]
LEFT OUTER JOIN [dbo].[Scans] AS [t5] ON [t5].[ID] = [t1].[PendingScanID]) ON [t1].[VItemID] = [t0].[ID]
LEFT OUTER JOIN ([dbo].[VItemMailAttributes] AS [t6]
LEFT OUTER JOIN [dbo].[VItemAddresses] AS [t7] ON [t7].[ID] = [t6].[SenderAddressID]) ON [t6].[VItemID] = [t0].[ID]
INNER JOIN [dbo].[Mailboxes] AS [t8] ON [t8].[ID] = [t0].[MailboxID]
) AS [t9]
WHERE ([t9].[MailboxFolderID] IS NULL) AND ((([t9].[PItemStatusEnumID] = 1) AND (([t9].[CurrentOpTypeEnumID] IS NULL) OR (NOT (([t9].[CurrentOpTypeEnumID]) IN (24, 34, 64, 65, 32))) OR ([t9].[CurrentOpExtractStatusEnumID] = 5))) OR ((EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Scans] AS [t10]
WHERE ([t10].[ScanStatusEnumID] = 4) AND ([t10].[PItemID] = [t9].[ID])
)) AND ([t9].[SystemFolderEnumID] = 0) AND ([t9].[PurgeScansStatusEnumID] = 10) AND ([t9].[IsScanOnly] = 1))) AND ([t9].[MailboxID] IN (11, 12, 13))
GROUP BY [t9].[value]";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(2, parsedSql.Select.Fields.Count);
Assert.Equal(1, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(3, parsedSql.Select.Wheres.Count);
}
static void Main(string[] args)
{
string sql = "select * from student m join Test n on m.id=n.id";
SqlParser parser = new SqlParser();
parser.Parse(sql);
Console.WriteLine(parser.ToText());
}
public void Massive()
{
string sql = @"select q.RowNumber, q.InboxID, q.PItemID, q.VItemID, q.LicensePlate, q.WeightOz, q.EstimatedPageCount, q.HeightInch, q.DepthInch, q.LengthInch, q.PitemStatusEnumId, q.CurrentOpTypeEnumId, q.CurrentOpExtractStatusEnumID, q.StorageFeeSuspended, q.StorageFeeDate, q.StorageFeeAmount, q.ReceivedDate, q.MailboxFolderID, q.SenderLabel, q.SenderAddress, q.FileStoreFileSetID, q.IsUnviewed, q.IsArchived, q.PieceSubType, q.FacilityStaticID, q.RecipientID, q.RecipientDisplayName, q.RecipientInboxID, q.ActualPageCount, q.ActualShipDate, q.ReceivedCarrierID, q.CarrierClassID, q.ScanID, q.PieceType, q.CheckCount, q.IsMICRSuspectCount, q.MailboxTypeEnumID, q.OperationPending, q.DepositRequestId, q.DepositRequestCompleted, q.ChecksFound, q.SequenceNumber, q.LocationID, q.LocationName, q.LocationCode, q.LocationType, q.ScanStatusEnumID, q.ScanStartDate, q.IsScanOnly from (SELECT i.ID AS InboxID, p.ID AS PItemID, v.ID AS VItemID, p.LicensePlate, p.WeightOz, p.EstimatedPageCount, p.HeightInch, p.DepthInch, p.LengthInch, p.PitemStatusEnumId, p.CurrentOpTypeEnumId, p.CurrentOpExtractStatusEnumID, p.StorageFeeSuspended AS StorageFeeSuspended, p.StorageFeeDate AS StorageFeeDate, p.StorageFeeAmount AS StorageFeeAmount, v.ReceivedDate, v.MailboxFolderID, v.SenderLabel, v.SenderAddress, v.FileStoreFileSetID, v.IsUnviewed, ISNULL(v.IsArchived, 0) AS IsArchived, LOWER(vte.Name) AS PieceSubType, f.StaticID AS FacilityStaticID, r.ID AS RecipientID, r.DisplayName AS RecipientDisplayName, r.InboxID AS RecipientInboxID, s.ActualPageCount, ship.ActualShipDate, vima.ReceivedCarrierID, vima.CarrierClassID, s.ID AS ScanID, 'mail' AS PieceType, ISNULL(depositChecks.CheckCount, 0) AS CheckCount, ISNULL(depositChecks.IsMICRSuspectCount, 0) AS IsMICRSuspectCount, m.MailboxTypeEnumID, CASE WHEN p.PItemStatusEnumID = 1 AND p.CurrentOpTypeEnumId IS NOT NULL AND p.CurrentOpExtractStatusEnumID IN (1, 2, 3) THEN 1 ELSE 0 END as OperationPending, pdr.ID AS DepositRequestId, pdr.CompletedDate AS DepositRequestCompleted, ISNULL(pdr.ChecksFound, 0) AS ChecksFound, pl.SequenceNumber, l.LocationID, l.Name AS LocationName, l.LocationCode, lte.Name AS LocationType, s.ScanStatusEnumID, s.StartDate AS ScanStartDate, p.IsScanOnly , ROW_NUMBER() OVER(ORDER BY v.ReceivedDate DESC) as 'RowNumber' FROM mailboxes m with (nolock) left join inboxes i with (nolock) on m.AccountID = i.AccountID join VItems v with (nolock) on v.MailboxID = m.ID LEFT JOIN VItemTypeEnum vte WITH (nolock) ON v.VItemTypeEnumID = vte.ID join PItems p with (nolock) on p.VItemID = v.ID join PItemLocations pl with (nolock) on pl.ID = p.PItemLocationID join Locations l with (nolock) on l.LocationID = pl.LocationID join LocationTypeEnum lte with (nolock) on l.LocationTypeEnumID = lte.ID join Facilities f with (nolock) on f.ID = l.FacilityID left join MailboxFolders mf with (nolock) on v.MailboxFolderID = mf.ID left join Recipients r with (nolock) on m.PrimaryRecipientID = r.ID and r.InboxID = i.ID OUTER APPLY (select top 1 * from Scans s2 with (nolock) where s2.PItemID = p.ID and s2.ScanStatusEnumID not in (5, 6, 7) order by s2.EndDate desc) s left join Shipments ship with (Nolock) on p.ShipmentID = ship.ID and ship.ActualShipDate IS NOT NULL join VItemMailAttributes vima with (Nolock) on vima.VItemID = v.ID left join (select VItemID, count(*) as 'CheckCount', sum(CAST(IsMICRSuspect AS INT)) as 'IsMICRSuspectCount' from DepositChecks depositChecks with(nolock) group by VItemID) depositChecks on depositChecks.VItemID = v.ID left join PItemDepositRequests pdr with (nolock) on pdr.PItemID = p.ID where i.ID = 186500 and ((p.PitemStatusEnumId in (1,9) and (p.CurrentOpTypeEnumID IS NULL OR p.CurrentOpTypeEnumID NOT IN (24,34,64,65,32) OR p.CurrentOpExtractStatusEnumID IN (5))) OR (p.PItemStatusEnumID IN (3,4) AND v.SystemFolderEnumID = 0 AND p.IsScanOnly = 1 AND p.PurgeScansStatusEnumID = 0 AND EXISTS (SELECT * FROM scans trashScans WITH (NOLOCK) where trashScans.PItemID = p.id AND trashScans.ScanStatusEnumID = 4) ) ) and (m.PrimaryRecipientID IS NULL OR r.InboxID = 186500 ) and ISNULL(v.IsArchived, 0) = 0 and v.MailboxFolderID is null and m.ID in (346622,346623,346624,346625,346626,346627,346628,361001,1865000,1865001,1865002,1865003,1865004,1865005,1865006,1865007,1865008,1865009,3466220,3466221 ) ) q where q.RowNumber >= 1 and q.RowNumber < 26 ORDER BY q.ReceivedDate DESC, q.PItemID DESC";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
}
public void SqlParserTest()
{
var sql = "select * from book where id=@id and ((etStart >= @datetime and etStart < dateadd(day,1,@datetime)) or (etStart <= @datetime and etEnd >= @datetime)) order by number asc,id desc";
var columns = SqlParser.Parse(sql);
Assert.AreEqual(1, columns.Select.Count());
Assert.AreEqual(3, columns.Where.Count());
Assert.AreEqual(2, columns.Order.Count());
}
public void Simple()
{
string sql = @"SELECT ID, Name, Description FROM ACCOUNTS a JOIN Users u on u.AccountID = a.ID WHERE a.ID = 1";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(3, parsedSql.Select.Fields.Count);
Assert.Equal(2, parsedSql.Select.Tables.Count);
Assert.Equal(1, parsedSql.Select.Joins.Count);
Assert.Equal(1, parsedSql.Select.Wheres.Count);
}
public void ThreeTables()
{
string sql = @"SELECT a.ID, b.Name, c.Description, c.Else FROM Table1 a JOIN Table2 b on a.Table2Id = b.Id JOIN Table3 c on c.Table3Id = b.id WHERE a.ID = 1 AND b.Something = 'Wow' OR c.Again = 11234";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(4, parsedSql.Select.Fields.Count);
Assert.Equal(3, parsedSql.Select.Tables.Count);
Assert.Equal(2, parsedSql.Select.Joins.Count);
Assert.Equal(3, parsedSql.Select.Wheres.Count);
}
public void SimpleWhereWithoutAlias()
{
string sql = @"SELECT name from accounts where id = 1";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(1, parsedSql.Select.Fields.Count);
Assert.Equal(1, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(1, parsedSql.Select.Wheres.Count);
Assert.Equal(0, parsedSql.Select.GroupBys.Count);
}
public void GroupBy()
{
string sql = @"SELECT a.Type, a.Class, Avg(a.Weight) from Accounts a Group By a.Type, a.Class";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(3, parsedSql.Select.Fields.Count);
Assert.Equal(1, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(0, parsedSql.Select.Wheres.Count);
Assert.Equal(2, parsedSql.Select.GroupBys.Count);
}
public void SimpleSubQuery()
{
string sql = @"SELECT a.id from (select id from Accounts) a";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(1, parsedSql.Select.Fields.Count);
Assert.Equal(1, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(0, parsedSql.Select.Wheres.Count);
Assert.Equal(0, parsedSql.Select.GroupBys.Count);
}
public void Compile()
{
SqlParser parser = new SqlParser();
if (parser.Parse(Sql) == false)
{
throw new Exception(string.Format("SQL parse error:\n\t{0}\nin statement\n\t{1}", parser.ErrorString, parser.ErrorLine));
}
SyntaxNode = parser.SyntaxTree as SelectNode;
SyntaxNode.Prepare();
OnCompile();
}
private static void ParseSql(DataTable data)
{
SqlParser sp = new SqlParser();
ParseResult pr = sp.Parse(data);
if (_OutputType.Equals("console"))
{
Console.WriteLine(Common.SerializeJson(pr, true));
Console.WriteLine("");
}
else if (_OutputType.Equals("file") && !String.IsNullOrEmpty(_OutputFile))
{
File.WriteAllBytes(_OutputFile, Encoding.UTF8.GetBytes(Common.SerializeJson(pr, true)));
}
}
private void fButtonModify_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(fTextBoxOriginal.Text.Trim()))
{
fTextBoxFinal.Text = string.Empty;
return;
}
try
{
SqlParser myParser = new SqlParser();
myParser.Parse(fTextBoxOriginal.Text);
if (!string.IsNullOrEmpty(fTextBoxWhereClause.Text.Trim()))
{
string myOrginalWhereClause = myParser.WhereClause;
if (string.IsNullOrEmpty(myOrginalWhereClause))
{
myParser.WhereClause = fTextBoxWhereClause.Text;
}
else
{
myParser.WhereClause = string.Format("({0}) AND ({1})", myOrginalWhereClause, fTextBoxWhereClause.Text);
}
}
if (!string.IsNullOrEmpty(fTextBoxOrderBy.Text.Trim()))
{
string myOrginalOrderByClause = myParser.OrderByClause;
if (string.IsNullOrEmpty(myOrginalOrderByClause))
{
myParser.OrderByClause = fTextBoxOrderBy.Text;
}
else
{
myParser.OrderByClause = string.Format("{0}, {1}", myOrginalOrderByClause, fTextBoxOrderBy.Text);
}
}
fTextBoxFinal.Text = myParser.ToText();
}
catch (Exception myEx)
{
MessageBox.Show(myEx.Message);
}
}
public void SimpleFunction()
{
string sql = @"SELECT SUM(
(CASE
WHEN [t9].[IsUnviewed] = 1 THEN 14
ELSE 15
END)) AS [UnreadCount], COUNT(*) AS [TotalCount]";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(2, parsedSql.Select.Fields.Count);
Assert.Equal("[unreadcount]", parsedSql.Select.Fields[0].Alias);
Assert.Equal("[totalcount]", parsedSql.Select.Fields[1].Alias);
Assert.Equal(0, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(0, parsedSql.Select.Wheres.Count);
}
public void MultipleFunctions()
{
string sql = @"SELECT SUM((CASE
WHEN a.type = 1 THEN 'a'
ELSE 'b'
END)) AS [UnreadCount], COUNT(*) AS [TotalCount]
FROM accounts a";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(2, parsedSql.Select.Fields.Count);
Assert.Equal("[unreadcount]", parsedSql.Select.Fields[0].Alias);
Assert.Equal("[totalcount]", parsedSql.Select.Fields[1].Alias);
Assert.Equal(1, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(0, parsedSql.Select.Wheres.Count);
}
protected virtual T Execute <T>(string sql, object parameters, string connectionName, Func <DbCommand, String, T> func)
{
//检查参数是否基元类型或值类型，int 和Int32之类
CheckArguments(sql, parameters);
ISqlStatement statement;
//sql变量是否是sqlid，若是直接提取sqlstatement,若否则需要sqlparser提取转换
bool isKey = FindStatement(sql, out statement);
Logger.Debug("\n------------Begin-----------\n");
if (isKey)
{
Logger.Debug("Dao -> Found Statement For Key : \n'{0}'", sql);
}
else
{
Logger.Debug("Dao -> Parse Statement For Sql : \n{0}", sql);
statement = SqlParser.Parse(sql);
// add to cache
DaoFactory.GetSqlSource().Add(sql, statement);
}
var sw = Stopwatch.StartNew();
try
{
connectionName = String.IsNullOrEmpty(connectionName) ? statement.Connection : connectionName;
return(func(CreateDbCommand(statement, parameters, connectionName), connectionName));
}
finally
{
sw.Stop();
if (isKey)
{
Logger.Info("Dao -> Execute Command '{0}' Used {1}ms", sql, sw.ElapsedMilliseconds);
}
else
{
Logger.Info("Dao -> Execute Sql Used {0}ms", sw.ElapsedMilliseconds);
}
Logger.Debug("\n------------End-----------\n");
}
}
private T Execute <T>(string sql, object parameters, Func <DbCommand, T> func)
{
CheckArguments(sql, parameters);
ISqlStatement statement;
//sql变量是否是sqlid，若是直接提取sqlstatement,若否则需要sqlparser提取转换
bool isKey = FindStatement(sql, out statement);
Logger.Debug("\n------------Begin-----------\n");
if (isKey)
{
Logger.Debug("Dao -> Found Statement For Key : \n'{0}'", sql);
}
else
{
Logger.Debug("Dao -> Parse Statement For Sql : \n{0}", sql);
statement = SqlParser.Parse(sql);
DaoFactory.GetSqlSource().Add(sql, statement);
}
var sw = Stopwatch.StartNew();
try
{
var command = statement.CreateCommand(Provider, parameters);
return(func(CreateDbCommand(command)));
}
finally
{
sw.Stop();
if (isKey)
{
Logger.Debug("Dao -> Execute Command '{0}' Used {1}ms", sql, sw.ElapsedMilliseconds);
}
else
{
Logger.Debug("Dao -> Execute Sql Used {0}ms", sw.ElapsedMilliseconds);
}
Logger.Debug("\n------------End-----------\n");
}
}
public void Case()
{
string sql = @"SELECT
(CASE
WHEN EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[UserAssociations] AS [t0]
WHERE ([t0].[UserID] = 240541) AND ([t0].[AccountID] = 364891)
) THEN 1
ELSE 0
END) AS [value]";
SqlParser sp = new SqlParser();
var parsedSql = sp.Parse(sql);
Assert.Equal(1, parsedSql.Select.Fields.Count);
Assert.Equal("[value]", parsedSql.Select.Fields[0].Alias);
Assert.Equal(0, parsedSql.Select.Tables.Count);
Assert.Equal(0, parsedSql.Select.Joins.Count);
Assert.Equal(0, parsedSql.Select.Wheres.Count);
}
public List <object> ExecuteSql(string sql)
{
List <object> result = new List <object>();
try
{
List <StatementBase> statements = SqlParser.Parse(sql);
foreach (StatementBase statement in statements)
{
result.Add(ExecuteStatement(statement));
}
return(result);
}
catch
{
if (Transaction != null)
{
Rollback();
}
throw;
}
}
protected SqlExpr Parse(string sql)
{
return(_sqlParser.Parse(sql));
}
public void Test_NonSqlString_WithSqlPaser()
{
var result = SqlParser.Parse("select a value from Hello Test");
Assert.IsTrue(result.Count == 0, "Non Sql should not parse and returns no errors.");
}
internal static void Run(SyntaxNodeAnalysisContext context, ExpressionSyntax token)
{
string id = token.ToFullString();
if (string.IsNullOrWhiteSpace(id))
{
return;
}
BlockSyntax method = context.Node.FirstAncestorOrSelf <BlockSyntax>();
if (method == null)
{
return;
}
try
{
if (token.IsKind(SyntaxKind.InvocationExpression))
{
var nodes = method.DescendantNodes();
string s = string.Empty;
foreach (SyntaxNode n in nodes)
{
if (n.IsKind(SyntaxKind.ExpressionStatement) && id.Contains(n.GetFirstToken().Text) && n.ToFullString().Contains("Append("))
{
string rm = n.GetFirstToken().Text + ".Append(";
s += n.GetText().ToString().Replace(rm, "").Replace(@""")", "").Replace("\r\n", "").Replace(";", "") + " ";
s = s.Replace(" \"", string.Empty);
}
}
s = Helper.BuildSqlStringFromIdString(context, s);
List <string> errorlist = SqlParser.Parse(s);
string errorlistText = String.Join("\r\n", errorlist);
var diagnostic2 = Diagnostic.Create(RuleParam, context.Node.GetLocation(), errorlistText);
context.ReportDiagnostic(diagnostic2);
return;
}
var t = method.DescendantTokens().Where <SyntaxToken>(tk => tk.ValueText != null && tk.IsKind(SyntaxKind.IdentifierToken) && tk.ValueText == id).First <SyntaxToken>();
if (string.IsNullOrWhiteSpace(t.ValueText))
{
return;
}
string sql = t.GetNextToken().GetNextToken().Value.ToString();
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
List <string> errors = SqlParser.Parse(sql);
if (errors.Count == 0)
{
var binaryExpressions = method.DescendantNodesAndSelf().OfType <BinaryExpressionSyntax>().First <BinaryExpressionSyntax>();
if (binaryExpressions != null)
{
BinaryExpressionDiagnostic.Run(context, binaryExpressions);
return;
}
return;
}
string errorText = String.Join("\r\n", errors);
var diagnostic = Diagnostic.Create(RuleParam, t.GetNextToken().GetNextToken().GetLocation(), errorText);
context.ReportDiagnostic(diagnostic);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("don't handle syntax yet: " + ex.Message);
}
}
private static async Task PostParse(RequestMetadata md)
{
string header = "[Komodo.Server] " + md.Http.Request.Source.IpAddress + ":" + md.Http.Request.Source.Port + " PostParse ";
if (String.IsNullOrEmpty(md.Params.Type))
{
_Logging.Warn(header + "no document type supplied");
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Supply 'type' [json/xml/html/sql/text] in querystring.", null, null).ToJson(true));
return;
}
byte[] data = null;
CrawlResult crawlResult = null;
ParseResult parseResult = null;
HttpCrawler httpCrawler = null;
FileCrawler fileCrawler = null;
if (!String.IsNullOrEmpty(md.Params.Url))
{
#region Crawl-URL
switch (md.Params.Type.ToLower())
{
case "html":
httpCrawler = new HttpCrawler(md.Params.Url);
crawlResult = httpCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied URL.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
HtmlParser htmlParser = new HtmlParser();
parseResult = htmlParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied URL.", null, parseResult).ToJson(true));
return;
}
break;
case "json":
httpCrawler = new HttpCrawler(md.Params.Url);
crawlResult = httpCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied URL.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
JsonParser jsonParser = new JsonParser();
parseResult = jsonParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied URL.", null, parseResult).ToJson(true));
return;
}
break;
case "text":
httpCrawler = new HttpCrawler(md.Params.Url);
crawlResult = httpCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied URL.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
TextParser textParser = new TextParser();
parseResult = textParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied URL.", null, parseResult).ToJson(true));
return;
}
break;
case "xml":
httpCrawler = new HttpCrawler(md.Params.Url);
crawlResult = httpCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied URL.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
XmlParser xmlParser = new XmlParser();
parseResult = xmlParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from URL " + md.Params.Url);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied URL.", null, parseResult).ToJson(true));
return;
}
break;
default:
_Logging.Warn(header + "invalid document type for processing via URL " + md.Params.Url);
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Invalid document type.", null, null).ToJson(true));
return;
}
md.Http.Response.StatusCode = 200;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(Common.SerializeJson(parseResult, md.Params.Pretty));
return;
#endregion
}
else if (!String.IsNullOrEmpty(md.Params.Filename))
{
#region Filename
switch (md.Params.Type.ToLower())
{
case "html":
fileCrawler = new FileCrawler(md.Params.Filename);
crawlResult = fileCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl filename " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied filename.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
HtmlParser htmlParser = new HtmlParser();
parseResult = htmlParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from file " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied filename.", null, parseResult).ToJson(true));
return;
}
break;
case "json":
fileCrawler = new FileCrawler(md.Params.Filename);
crawlResult = fileCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl filename " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied filename.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
JsonParser jsonParser = new JsonParser();
parseResult = jsonParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from file " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied filename.", null, parseResult).ToJson(true));
return;
}
break;
case "text":
fileCrawler = new FileCrawler(md.Params.Filename);
crawlResult = fileCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl filename " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied filename.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
TextParser textParser = new TextParser();
parseResult = textParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from file " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied filename.", null, parseResult).ToJson(true));
return;
}
break;
case "xml":
fileCrawler = new FileCrawler(md.Params.Filename);
crawlResult = fileCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl filename " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl supplied filename.", null, crawlResult).ToJson(true));
return;
}
data = crawlResult.Data;
XmlParser xmlParser = new XmlParser();
parseResult = xmlParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from file " + md.Params.Filename);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied filename.", null, parseResult).ToJson(true));
return;
}
break;
default:
_Logging.Warn(header + "invalid document type for processing via filename " + md.Params.Filename);
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Invalid document type.", null, null).ToJson(true));
return;
}
md.Http.Response.StatusCode = 200;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(Common.SerializeJson(parseResult, md.Params.Pretty));
return;
#endregion
}
else if (md.Params.Type.ToLower().Equals("sql"))
{
#region Query
if (md.Http.Request.Data == null || md.Http.Request.ContentLength < 1)
{
_Logging.Warn(header + "no query found in payload");
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "No SQL query in request payload.", null, null).ToJson(true));
return;
}
DbSettings dbSettings = new DbSettings(md.Params.DbType, md.Params.DbServer, md.Params.DbPort, md.Params.DbUser, md.Params.DbPass, md.Params.DbInstance, md.Params.DbName);
SqlCrawler sqlCrawler = new SqlCrawler(dbSettings, Encoding.UTF8.GetString(Common.StreamToBytes(md.Http.Request.Data)));
crawlResult = sqlCrawler.Get();
if (!crawlResult.Success)
{
_Logging.Warn(header + "failed to crawl database " + md.Params.DbName);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to crawl specified database.", null, crawlResult).ToJson(true));
return;
}
SqlParser sqlParser = new SqlParser();
parseResult = sqlParser.Parse(crawlResult.DataTable);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from database " + md.Params.DbName);
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from specified database.", null, parseResult).ToJson(true));
return;
}
md.Http.Response.StatusCode = 200;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(Common.SerializeJson(parseResult, md.Params.Pretty));
return;
#endregion
}
else if (md.Http.Request.Data != null && md.Http.Request.ContentLength > 0)
{
#region Supplied-Data
data = Common.StreamToBytes(md.Http.Request.Data);
switch (md.Params.Type.ToLower())
{
case "html":
HtmlParser htmlParser = new HtmlParser();
parseResult = htmlParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from supplied data");
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied data.", null, parseResult).ToJson(true));
return;
}
break;
case "json":
JsonParser jsonParser = new JsonParser();
parseResult = jsonParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from supplied data");
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied data.", null, parseResult).ToJson(true));
return;
}
break;
case "text":
TextParser textParser = new TextParser();
parseResult = textParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from supplied data");
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied data.", null, parseResult).ToJson(true));
return;
}
break;
case "xml":
XmlParser xmlParser = new XmlParser();
parseResult = xmlParser.ParseBytes(data);
if (!parseResult.Success)
{
_Logging.Warn(header + "failed to parse data from supplied data");
md.Http.Response.StatusCode = 500;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Failed to parse data from supplied data.", null, parseResult).ToJson(true));
return;
}
break;
default:
_Logging.Warn(header + "invalid document type for processing via data");
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Invalid document type supplied.", null, null).ToJson(true));
return;
}
md.Http.Response.StatusCode = 200;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(Common.SerializeJson(parseResult, md.Params.Pretty));
return;
#endregion
}
else
{
#region Unknown
_Logging.Warn(header + "unable to derive data source from request");
md.Http.Response.StatusCode = 400;
md.Http.Response.ContentType = "application/json";
await md.Http.Response.Send(new ErrorResponse(400, "Unable to derive data source from request.", null, null).ToJson(true));
return;
#endregion
}
}
public void parse_sql_batch(string batch, string[] expected)
{
var parser = new SqlParser();
parser.Parse(batch).Should().Equal(expected);
}
internal static void Run(SyntaxNodeAnalysisContext context, ExpressionSyntax token)
{ //Check String
string id = token.ToFullString();
if (string.IsNullOrWhiteSpace(id))
{
return;
}
//Check BlockSyntax
BlockSyntax method = context.Node.FirstAncestorOrSelf <BlockSyntax>();
if (method == null)
{
return;
}
try
{ //Check InvocationExpression case
if (token.IsKind(SyntaxKind.InvocationExpression))
{
var nodes = method.DescendantNodes();
string s = string.Empty;
foreach (SyntaxNode n in nodes)
{ //Check ExpressionStatement case and have Contains("Appand("). , Build String
if (n.IsKind(SyntaxKind.ExpressionStatement) && id.Contains(n.GetFirstToken().Text) && n.ToFullString().Contains("Append("))
{
string rm = n.GetFirstToken().Text + ".Append(";
s += n.GetText().ToString().Replace(rm, "").Replace(@""")", "").Replace("\r\n", "").Replace(";", "") + " ";
s = s.Replace(" \"", string.Empty);
}
}
//Send to class Helper and Report error
s = Helper.BuildSqlStringFromIdString(context, s);
List <string> errorlist = SqlParser.Parse(s);
string errorlistText = String.Join("\r\n", errorlist);
var diagnostic2 = Diagnostic.Create(Rule, context.Node.GetLocation(), errorlistText);
context.ReportDiagnostic(diagnostic2);
return;
}
//DescendantTokens where SyntaxToken
t = method.DescendantTokens().Where <SyntaxToken>(tk => tk.ValueText != null && tk.IsKind(SyntaxKind.IdentifierToken) && tk.ValueText == id).First <SyntaxToken>();
if (string.IsNullOrWhiteSpace(t.ValueText))
{
return;
}
sql = t.GetNextToken().GetNextToken().Value.ToString();
if (sql != null)
{
AzureML(sql);
SendData(getJson);
while (getResult == "")
{
}
Deserialize(getResult);
getResult = "";
}
if (string.IsNullOrWhiteSpace(sql))
{
return;
}
List <string> errors = SqlParser.Parse(sql);
if (errors.Count == 0)
{ //Check BinaryExpression case
var binaryExpressions = method.DescendantNodesAndSelf().OfType <BinaryExpressionSyntax>().First <BinaryExpressionSyntax>();
if (binaryExpressions != null)
{ //Send to class BinaryExpressionDiagnostic
BinaryExpressionDiagnostic.Run(context, binaryExpressions);
return;
}
return;
}
//Report error
string errorText = String.Join("\r\n", errors);
var diagnostic = Diagnostic.Create(Rule, t.GetNextToken().GetNextToken().GetLocation(), errorText);
context.ReportDiagnostic(diagnostic);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("don't handle syntax yet: " + ex.Message);
}
//Report warning union query injection
if (reportWarningUnionquery != "0")
{
Diagnostics.UnionQueryExpression.Run(context, t);
}
//Report warning illegal query injection
if (reportWarningIllegalquery != "0")
{
Diagnostics.IllegalExpression.Run(context, t);
}
//Report warning piggybacked query injection
if (reportWarningPiggybackedquery != "0")
{
Diagnostics.PiggyBackedExpression.Run(context, t);
}
}