/// <summary>
/// Determines if a statement is a select, which is not selecting into a table. Therefore it
/// returns a result set.
/// </summary>
private static bool IsPlainSelect(SqlStatement statement)
{
var statementTypeName = statement.GetType().ToString().Split('.').Last();
if (statementTypeName == "SqlSelectStatement")
{
var xmlDoc = new XmlDocument();
xmlDoc.LoadXml(statement.Xml);
if (TryParseFirstTagAttribute(xmlDoc, "SqlSelectIntoClause", "IntoTarget", out var _) == false)
{
return(true);
}
}
return(false);
}
private static bool StatementIsSafe(SqlStatement statement, out string message)
{
var statementTypeName = statement.GetType().ToString().Split('.').Last();
Console.WriteLine(statementTypeName);
var xmlDoc = new XmlDocument();
xmlDoc.LoadXml(statement.Xml);
switch (statementTypeName)
{
case "SqlSelectStatement":
if (TryParseFirstTagAttribute(xmlDoc, "SqlSelectIntoClause", "IntoTarget", out string selectIntoTarget))
{
if (selectIntoTarget.Contains('#', StringComparison.InvariantCultureIgnoreCase) == false)
{
message = "Select Into a real table";
return(false);
}
message = "Select Into a temp table";
return(true);
}
message = "Plain Select Statement";
return(true);
case "SqlDeleteStatement":
var delete = xmlDoc.GetElementsByTagName("SqlDeleteStatement")[0];
if (TryParseFirstTagAttribute(xmlDoc, "SqlIdentifier", "Value", out string deleteTarget))
{
if (deleteTarget.Contains('#', StringComparison.InvariantCultureIgnoreCase) == false)
{
message = "Delete from a real table";
return(false);
}
message = "Delete from a temp table";
return(true);
}
message = "Error determining if SQL is safe";
return(false);
case "SqlDropTableStatement":
var drop = xmlDoc.GetElementsByTagName("SqlDropTableStatement")[0];
if (TryParseFirstTagAttribute(xmlDoc, "SqlIdentifier", "Value", out string dropTarget))
{
if (dropTarget.Contains('#', StringComparison.InvariantCultureIgnoreCase) == false)
{
message = "Dropping a real table";
return(false);
}
message = "Dropping a temp table";
return(true);
}
message = "Error determining if SQL is safe";
return(false);
case "SqlUpdateStatement":
var update = xmlDoc.GetElementsByTagName("SqlUpdateStatement")[0];
if (TryParseFirstTagAttribute(xmlDoc, "SqlIdentifier", "Value", out string updateTarget))
{
if (updateTarget.Contains('#', StringComparison.InvariantCultureIgnoreCase) == false)
{
message = "Updating a real table";
return(false);
}
message = "Updating a temp table";
return(true);
}
message = "Error determining if SQL is safe";
return(false);
case "SqlInsertStatement":
var insert = xmlDoc.GetElementsByTagName("SqlInsertStatement")[0];
if (TryParseFirstTagAttribute(xmlDoc, "SqlIdentifier", "Value", out string insertTarget))
{
if (insertTarget.Contains('#', StringComparison.InvariantCultureIgnoreCase) == false)
{
message = "Insertting to a real table";
return(false);
}
message = "Insertting to a temp table";
return(true);
}
message = "Error determining if SQL is safe";
return(false);
default:
message = "Uknown Statement Type";
return(false);
}
}
public void WriteStatement(SqlStatement stmt, bool isLastStatement, StringBuilder sb)
{
switch (stmt)
{
case SqlInsert insert:
WriteInsertQuery(insert.Inserts, insert.TableName, insert.AutoIncrementPrimaryKeyName, isLastStatement, sb);
return;
case SqlInsertSelect insertSelect:
WriteInsertQuery(insertSelect.FromSource, insertSelect.Inserts, insertSelect.TableName, insertSelect.AutoIncrementPrimaryKeyName, isLastStatement, sb);
return;
case SqlSelect select:
WriteSelectQuery(select.FromSource, sb);
sb.AppendLine(";");
return;
case SqlUpdate update:
WriteUpdateQuery(update.Inserts, update.FromSource, sb);
sb.AppendLine(";");
return;
case SqlDelete delete:
WriteDeleteQuery(delete.FromSource, sb);
sb.AppendLine(";");
return;
case SqlDeclareVariable declareVariable:
WriteDeclareSqlVariable(declareVariable.VariableName, declareVariable.VariableType, declareVariable.SqlTypeInfo, sb);
return;
case SqlSet set:
WriteSetSqlVariable(set.Variable, set.Expression, sb);
return;
case SqlCreateTable createTable:
WriteCreateTable(createTable.TableName, createTable.Columns, sb);
return;
case SqlDropTable dropTable:
WriteDropTable(dropTable.TableName, true, sb);
return;
case SqlAddColumn addColumn:
WriteAddColumn(addColumn.TableName, addColumn.Column, sb);
return;
case SqlDropColumn dropColumn:
WriteDropColumn(dropColumn.TableName, dropColumn.ColumnName, sb);
return;
case SqlAddForeignKey addForeignKey:
WriteAddForeignKey(addForeignKey.TableName, addForeignKey.ForeignKey, sb);
return;
case SqlDropForeignKey dropForeignKey:
WriteDropForeignKey(dropForeignKey.TableName, dropForeignKey.ForeignKeyName, sb);
return;
case SqlAddIndex addIndex:
WriteAddIndex(addIndex.TableName, addIndex.Index, sb);
return;
case SqlDropIndex dropIndex:
WriteDropIndex(dropIndex.TableName, dropIndex.IndexName, sb);
return;
}
throw new Exception("Unsupported statement " + stmt.GetType().Name);
}