public static bool ValidateTargetDatabase(DynamicStatement dynSt, IConfiguration _configuration)
{
string environment = dynSt.environment.ToLower();
var allowed_dbs = _configuration["DataAccess:" + environment + ":allowed_dbs"].Split(';').ToList();
return(allowed_dbs.Contains(dynSt.database));
}
public static bool ValidateTargetEnviroment(DynamicStatement dynSt)
{
string environment = dynSt.environment.ToLower();
if (String.Equals(environment, "development"))
{
return(true);
}
if (String.Equals(environment, "production"))
{
return(true);
}
return(false);
}
public static bool ValidateStatement(DynamicStatement dynSt, string verb)
{
string statement = dynSt.statement.ToLower();
switch (verb)
{
case "SELECT":
if (!statement.Contains("select "))
{
return(false);
}
return(!ContainsReservedWordsExcept(statement, "select "));
case "INSERT":
if (!statement.Contains("insert "))
{
return(false);
}
return(!ContainsReservedWordsExcept(statement, "insert into "));
case "UPDATE":
if (!statement.Contains("update "))
{
return(false);
}
if (!statement.Contains("where "))
{
return(false);
}
return(!ContainsReservedWordsExcept(statement, "update "));
case "DELETE":
if (!statement.Contains("delete from "))
{
return(false);
}
if (!statement.Contains("where "))
{
return(false);
}
return(!ContainsReservedWordsExcept(statement, "delete from "));
}
return(false);
}
private IActionResult PerformHelper(DynamicStatement dynSt, string verb)
{
if (!RulesValidationService.ValidateTargetEnviroment(dynSt))
{
return(BadRequest("O ambiente informado é inválido."));
}
if (!RulesValidationService.ValidateTargetDatabase(dynSt, _configuration))
{
return(BadRequest("O banco de dados informado não é acessível por este serviço."));
}
if (!RulesValidationService.ValidateStatement(dynSt, verb))
{
return(BadRequest("O statement declarado não corresponde a um comando " + verb + " válido."));
}
string executionResult = DataAccessService.performStatement(dynSt, verb, _configuration);
if (executionResult.Contains("Falha na execução: "))
{
return(BadRequest(executionResult));
}
return(Ok(executionResult));
}
public static string performStatement(DynamicStatement dynSt, string verb, IConfiguration _configuration)
{
string dataSource = _configuration["DataAccess:" + dynSt.environment.ToLower() + ":data_source"];
string userid = _configuration["DataAccess:" + dynSt.environment.ToLower() + ":user_id"];
string password = _configuration["DataAccess:" + dynSt.environment.ToLower() + ":password"];
string connectionString = $"Data Source={dataSource};Initial Catalog={dynSt.database};User ID={userid};Password={password};";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(dynSt.statement, connection);
try
{
connection.Open();
if (String.Equals(verb, "SELECT"))
{
SqlDataReader reader = command.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
return(JsonConvert.SerializeObject(dataTable));
}
else
{
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
return("O comando foi executado, mas nenhum registro foi afetado.");
}
else
{
return("O comando foi executado com sucesso. " + rowsAffected.ToString() + " registros afetados.");
}
}
}
catch (Exception ex)
{
return("Falha na execução: " + ex.Message);
}
}
}
public IActionResult PerformDelete(DynamicStatement dynSt)
{
return(PerformHelper(dynSt, "DELETE"));
}
public IActionResult PerformUpdate(DynamicStatement dynSt)
{
return(PerformHelper(dynSt, "UPDATE"));
}
public IActionResult PerformInsert(DynamicStatement dynSt)
{
return(PerformHelper(dynSt, "INSERT"));
}
public IActionResult PerformSelect(DynamicStatement dynSt)
{
return(PerformHelper(dynSt, "SELECT"));
}
