private void ParseDelete(string query)
{
TSqlFragment tree = InitiateTSql110Parser(query);
if (SyntaxError)
{
return;
}
if (tree is TSqlScript)
{
TSqlScript tSqlScript = tree as TSqlScript;
foreach (TSqlBatch sqlBatch in tSqlScript.Batches)
{
foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
{
ParseSqlDeleteStatement(sqlStatement);
}
}
}
RbacTSqlFragmentVisitor rbacTSqlFragmentVisitor = new RbacTSqlFragmentVisitor();
tree.Accept(rbacTSqlFragmentVisitor);
this.Warnings.AddRange(rbacTSqlFragmentVisitor.Warnings);
TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
IsParsed = true;
}
private void AddRelationalJoins(RbacRelation relation, List <RbacCondition> conditions)
{
//was relation already referred in the query?
RbacTable relationTable = TablesReferred.Find(relation.WithTable);
if (relationTable == null)
{
//NO:
//Is there already join with that table?
RbacJoin join = JoinClauses.JoinExists(relation.WithTable, relation.SelfName);
if (join == null)
{
//add new join, at the end of operation we will stringify sequentially
join = RbacJoin.AddNewJoin(this,
relation.SelfName, relation.SelfColumnName,
relation.WithTable, relation.WithColumn);
}
//add into referred table as 'ReferencedOnly'
relationTable = new RbacTable(string.Empty, relation.WithTable, false);
relationTable.Conditions.AddRange(conditions);
relationTable.ReferencedOnly = true;
relationTable.TempAlias = join.WithTableAlias;
TablesReferred.Add(relationTable);
}
//add condition
ApplyCondition(relationTable);
}
public void ApplyPermissionUpdate()
{
var tables = Columns.GroupBy(c => c.Table.Name).Select(grp => grp.ToList()).ToList();
foreach (var allColumnnsInATable in tables)
{
if (allColumnnsInATable.Count > 0)
{
RbacTable rbacTable = TablesReferred.Find(allColumnnsInATable[0].Table.Name);
if (rbacTable == null)
{
throw new Exception("Could not find table name in referred tables!");
}
if (rbacTable.AllowedOperations.HasFlag(RbacDBOperations.Update))
{
foreach (RbacSelectColumn column in allColumnnsInATable)
{
RbacColumn rbacColumn = rbacTable.FindColumn(column.Name);
if (!rbacColumn.AllowedOperations.HasFlag(RbacDBOperations.Update))
{
RbacException.Raise(string.Format("User '{0}' has permission to update table '{1}', however has no permission to update column '{2}'!",
Context.User.UserName, rbacTable.Name, rbacColumn.Name), RbacExceptionCategories.Parser);
}
}
}
else
{
RbacException.Raise(string.Format("User '{0}' does not have permission to update table '{1}'!",
Context.User.UserName, rbacTable.Name), RbacExceptionCategories.Parser);
}
}
}
IsPermissionApplied = true;
}
private void ApplyConditions()
{
ConditionAppliedOn.Clear();
List <RbacTable> tablesWithCondition = TablesReferred.Where(tr => tr.Conditions.Count > 0).ToList();
foreach (RbacTable table in tablesWithCondition)
{
ApplyCondition(table);
}
}
public bool ParseUsingSqlCommand()
{
if (TablesReferred == null)
{
TablesReferred = new List <RbacTable>();
}
else
{
TablesReferred.Clear();
}
try
{
using (SqlConnection connection = new SqlConnection(Context.ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(OriginalQuery, connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
//if (row["BaseTableName"].ToString() == "City")
// Debugger.Break();
RbacSelectColumn column = new RbacSelectColumn();
column.Alias = row["ColumnName"].ToString();
column.Name = row["BaseColumnName"].ToString();
column.Table.Name = row["BaseTableName"].ToString();
Columns.Add(column);
RbacTable table = Context.User.Role.CrudPermissions.Find(column.Table.Name);
if (table != null)
{
TablesReferred.Add(table);
}
else
{
throw new Exception(string.Format("The referred table {0} was not found in meta data!", row["BaseTableName"].ToString()));
}
}
TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
connection.Close();
}
ParsedMethod = RbacSelectQueryParsedMethods.CommandBehavior;
ParsedQuery = ParsedQuery.Replace("*", Columns.ToCommaSeparatedString());
IsParsed = true;
return(true);
}
catch (Exception ex)
{
Errors.Add(ex.Message);
}
return(false);
}
private void UpdateReferredTables(string tableName, string tableAlias)
{
RbacTable actualTable = Context.User.Role.CrudPermissions.Find(tableName);
actualTable.Alias = tableAlias;
if (actualTable != null)
{
TablesReferred.Add(actualTable);
}
else
{
RbacException.Raise(string.Format("The referred table {0} was not found in meta data!", tableName),
RbacExceptionCategories.Parser);
}
}
private void UpdateReferredTables(List <RbacSelectColumn> columns)
{
foreach (RbacSelectColumn column in columns)
{
if (string.IsNullOrEmpty(column.Table.Name))
{
RbacTable table = TablesReferred.Find(column.Table.Alias);
if (table == null)
{
UpdateReferredTables(column.Table.Name, column.Table.Alias);
}
else
{
column.Table = table;
}
}
}
}
private void ParseInsert(string query)
{
TSqlFragment tree = InitiateTSql110Parser(query);
if (SyntaxError)
{
return;
}
if (tree is TSqlScript)
{
TSqlScript tSqlScript = tree as TSqlScript;
foreach (TSqlBatch sqlBatch in tSqlScript.Batches)
{
foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
{
ParseSqlInsertStatement(sqlStatement);
}
}
}
//tree.Accept(new RbacTSqlFragmentVisitor());
TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
IsParsed = true;
}
private void ParseSqlUpdateStatement(TSqlStatement sqlStatement)
{
if (sqlStatement.GetType() == typeof(UpdateStatement))
{
UpdateStatement aUpdateStatement = (UpdateStatement)sqlStatement;
#region Handle Target Table
#endregion Handle Target Table
RbacTable targetTable = new RbacTable();
#region Handle From Clause - When Update with Join
if (aUpdateStatement.UpdateSpecification.FromClause != null)
{
//mostly update with join case
NamedTableReferenceVisitor fromClauseNtVisitor = new NamedTableReferenceVisitor(Context);
aUpdateStatement.UpdateSpecification.FromClause.AcceptChildren(fromClauseNtVisitor);
this.TablesReferred = fromClauseNtVisitor.Tables;
if (TablesReferred.Count > 0)
{
targetTable = TablesReferred[0];
//if alias is being updated, we need to fix table name
RbacTable tryTable = Context.User.Role.CrudPermissions.Find(targetTable.Name);
if (tryTable == null)
{
//alias is being updated, lets get the actual table name
var tt = fromClauseNtVisitor.Tables.Where(t => t.Alias == targetTable.Name).ToList()[0];
if (tt != null)
{
targetTable.Name = tt.Name;
}
}
}
else
{
RbacException.Raise("No target table found in the update query!");
}
}
else
{
NamedTableReferenceVisitor ntVisitor = new NamedTableReferenceVisitor(Context);
aUpdateStatement.UpdateSpecification.Target.Accept(ntVisitor);
if (ntVisitor.Tables.Count == 0)
{
RbacException.Raise("No target table found in the update query!");
}
else if (ntVisitor.Tables.Count == 1)
{
targetTable = ntVisitor.Tables[0];
}
else
{
RbacException.Raise("More than 1 target tables found in the update query! Currently not supported.");
}
TablesReferred.Add(targetTable);
}
#endregion Handle From Clause - When Update with Join
#region Handle Columns
SetClauseVisitor scVisitor = new SetClauseVisitor(targetTable.Name);
aUpdateStatement.UpdateSpecification.AcceptChildren(scVisitor);
Columns = scVisitor.Columns;
UpdateReferredTables(Columns);
#endregion Handle Columns
}
else
{
Errors.Add("Not a update statement!");
}
}
private void ParseSqlSelectStatement(TSqlStatement sqlStatement)
{
if (sqlStatement.GetType() == typeof(SelectStatement))
{
SelectStatement aSelectStatement = (SelectStatement)sqlStatement;
QueryExpression aQueryExpression = aSelectStatement.QueryExpression;
if (aQueryExpression.GetType() == typeof(QuerySpecification))
{
QuerySpecification aQuerySpecification = (QuerySpecification)aQueryExpression;
if (aQuerySpecification.FromClause == null)
{
return; //'select *' is valid query, but not of our interest
}
//tables
NamedTableReferenceVisitor ntVisitor = new NamedTableReferenceVisitor(Context);
aQuerySpecification.FromClause.Accept(ntVisitor);
TablesReferred = ntVisitor.Tables;
//columns
ScalarExpressionVisitor seVisitor = null;
foreach (SelectElement selectStatement in aQuerySpecification.SelectElements)
{
seVisitor = new ScalarExpressionVisitor(Context, TablesReferred[0]);
selectStatement.Accept(seVisitor);
Columns.AddRange(seVisitor.Columns);
}
if (!string.IsNullOrEmpty(seVisitor.ParsedQuery))
{
ParsedQuery = seVisitor.ParsedQuery; //todo
}
//else
// ParsedQuery = Columns.GetParsedQuery(ParsedQuery);
UpdateReferredTables(Columns);
//ensure unique tables
TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
//joins
JoinClauseVisitor jcVisitor = new JoinClauseVisitor(Context);
aQueryExpression.AcceptChildren(jcVisitor);
JoinClauses = jcVisitor.JoinClauses;
//where clause
if (aQuerySpecification.WhereClause != null)
{
EqualVisitor cidv = new EqualVisitor(ParsedQuery);
InPredicateVisitor inpv = new InPredicateVisitor(ParsedQuery);
aQuerySpecification.WhereClause.AcceptChildren(cidv);
aQuerySpecification.WhereClause.AcceptChildren(inpv);
WhereClauses.AddRange(cidv.WhereClauses);
WhereClauses.AddRange(inpv.WhereClauses);
WhereClauses.ParseReferenceTableNames(JoinClauses);
}
}
}
else
{
Errors.Add("Not a select statement!");
}
}
