/// <summary> /// Execute query. Pass isscalar when running identity queries /// </summary> /// <param name="query"></param> /// <param name="isScalar"></param> /// <returns></returns> public async Task <int> ExecuteQuery(string query, bool isScalar) { using (Microsoft.Data.SqlClient.SqlConnection cn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString)) { await cn.OpenAsync(); using (Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand(query)) { cmd.CommandTimeout = CommandTimeOut; cmd.Connection = cn; if (!isScalar) { var result = await cmd.ExecuteNonQueryAsync(); return(result); } else { var result = await cmd.ExecuteScalarAsync(); return(Convert.ToInt32(result)); } } } }
public async Task <IActionResult> PromoteStudents(EnrStud input) { SqlConnection conn = new SqlConnection(_connectionString); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = @"select e.IdEnrollment, e.Semester, e.StartDate, s.IdStudy, s.Name from Enrollment e left join Studies s on s.IdStudy = e.IdStudy where s.Name = @studyName and e.Semester = @semester"; com.Parameters.AddWithValue("studyName", input.Studies); com.Parameters.AddWithValue("semester", input.Semester); conn.Open(); SqlDataReader dataReader = await com.ExecuteReaderAsync(); await dataReader.ReadAsync(); Enrollment enr = new Enrollment { IdEnrollment = int.Parse(dataReader["IdEnrollment"].ToString()), Semester = int.Parse(dataReader["Semester"].ToString()), StartDate = DateTime.Parse(dataReader["StartDate"].ToString()), Study = new Study { IdStudy = int.Parse(dataReader["IdStudy"].ToString()), Name = dataReader["Name"].ToString(), } }; if (enr == null) { return(NotFound()); } conn = new SqlConnection(_connectionString); com = new SqlCommand(); com.Connection = conn; com.CommandType = System.Data.CommandType.StoredProcedure; com.CommandText = "PromoteStudents"; com.Parameters.AddWithValue("studies", input.Studies); com.Parameters.AddWithValue("semester", input.Semester); conn.Open(); await com.ExecuteNonQueryAsync(); return(StatusCode(200)); }
public int Save(PCAxis.Query.SavedQuery query, int?id) { query.Stats = null; string pxsjson = JsonConvert.SerializeObject(query); using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString)) { conn.Open(); var cmd = new Microsoft.Data.SqlClient.SqlCommand( @"insert into SavedQueryMeta ( DataSourceType, DatabaseId, DataSourceId, [Status], StatusUse, StatusChange, OwnerId, MyDescription, CreatedDate, SavedQueryFormat, SavedQueryStorage, QueryText, Runs, Fails ) values ( @databaseType, @databaseId, @mainTable, 'A', 'P', 'P', 'Anonymous', @title, @creationDate, 'PXSJSON', 'D', @query, 0, 0 ); SELECT @@IDENTITY AS 'Identity';", conn); cmd.Parameters.AddWithValue("databaseType", query.Sources[0].Type); cmd.Parameters.AddWithValue("databaseId", query.Sources[0].DatabaseId); cmd.Parameters.AddWithValue("mainTable", GetMaintable(query.Sources[0])); cmd.Parameters.AddWithValue("title", ""); cmd.Parameters.AddWithValue("creationDate", DateTime.Now); cmd.Parameters.AddWithValue("query", pxsjson); int newid = Convert.ToInt32(cmd.ExecuteScalar()); return(newid); } return(-1); }
public static List <Decrypted_logline> get_database_logs() { try { string queryString = "select * from logs"; var command = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection); if (command.Connection.State != System.Data.ConnectionState.Open) { command.Connection.Open(); } command.ExecuteNonQuery(); var reader = command.ExecuteReader(); List <Decrypted_logline> logs = new List <Decrypted_logline>(); while (reader.Read()) { //DEFINE BYTE LENGTH //byte[] EncryptedBytes = new byte[1024]; //reader.GetBytes(1, 0, EncryptedBytes, 0, 1024); byte[] EncryptedBytes = (byte[])reader[2]; //count non-zero end bytes int nonZeroBytes = 0; for (int i = EncryptedBytes.Length - 1; i > -1; i--) { if (EncryptedBytes[i] == 0x00) { nonZeroBytes++; } else { break; } } var DecryptedBytes = AesInst.CreateDecryptor().TransformFinalBlock(EncryptedBytes, 0, EncryptedBytes.Length - nonZeroBytes); var DecryptedChars = Encoding.UTF8.GetChars(DecryptedBytes); var Decrypted_Text = new string(DecryptedChars); logs.Add(new Decrypted_logline(reader.GetDateTime(3), reader.GetString(1), Decrypted_Text)); } command.Connection.Close(); return(logs); } catch { string queryString = "select * from logs"; var command = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection); if (command.Connection.State == System.Data.ConnectionState.Open) { command.Connection.Close(); } return(new List <Decrypted_logline>()); } }
public bool MarkAsFailed(int id) { using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString)) { conn.Open(); var cmd = new Microsoft.Data.SqlClient.SqlCommand("update SavedQueryMeta set UsedDate = @lastUsed, Runs = Runs + 1, Fails = Fails + 1 where QueryId = @queryId", conn); cmd.Parameters.AddWithValue("queryId", id); cmd.Parameters.AddWithValue("lastUsed", DateTime.Now); return(cmd.ExecuteNonQuery() == 1); } }
public PCAxis.Query.SavedQuery Load(int id) { using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString)) { conn.Open(); var cmd = new Microsoft.Data.SqlClient.SqlCommand("select QueryText from SavedQueryMeta where QueryId = @queryId", conn); cmd.Parameters.AddWithValue("queryId", id); string query = cmd.ExecuteScalar() as string; PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery; return(sq); } return(null); }
public IEnumerable <IndicadoresCorporativos> ObterIndicadoresCorporativos(long idProjeto) { var result = new List <IndicadoresCorporativos>(); Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); string sql = @"select a.Id, a.Identificador, a.Nome, a.TipoCalculo, case when b.IdIndicador is null then 0 else 1 end Vinculado, B.Id ID2 from Indicador a left join (select IdIndicador, Id from ProjetoEstruturaOrganizacional where IdProjeto = @p1 and Tipo = 2) b on (a.Id = b.IdIndicador) where a.Corporativo = 1 order by a.Nome"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sql, connection)) { try { command.Parameters.AddWithValue("p1", idProjeto); using var reader = command.ExecuteReader(); while (reader.Read()) { result.Add(new IndicadoresCorporativos { Id = reader.GetInt64(0), Identificador = reader.GetString(1), Nome = reader.GetString(2), TipoCalculo = reader.GetInt32(3), Vinculado = reader.GetInt32(4) == 1, IdProjetoEstruturaOrganizacional = reader.IsDBNull(5) ? null : (long?)reader.GetInt64(5) }); } } catch (Exception exc) { excSql = exc; } } connection.Close(); if (excSql != null) { throw excSql; } return(result); }
private void CorrigirOrdens(long idSuperior) { var itens = Filter(item => item.IdSuperior == idSuperior).OrderBy(it => it.Ordem).ToList(); var strBuilder = new StringBuilder(); short ordem = 0; foreach (var it in itens) { ordem++; if (ordem != it.Ordem) { strBuilder.AppendLine($"update ProjetoEstruturaOrganizacional set Ordem = {ordem} where Id = {it.Id};"); } } if (strBuilder.Length > 0) { Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); using (var command = new Microsoft.Data.SqlClient.SqlCommand(strBuilder.ToString(), connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } } }
public static int log_error(DateTime logDateTime, string labnumber, string logtext) { try { if (logtext.Length >= 256 || labnumber.Length >= 40) { throw new Exception("Too long!"); } var log_in_char = logtext.ToArray(); byte[] log_bytes = Encoding.UTF8.GetBytes(log_in_char); var encrypted_log = AesInst.CreateEncryptor().TransformFinalBlock(log_bytes, 0, log_bytes.Length); string encrypted_log_HEX = "0x"; foreach (byte part in encrypted_log) { encrypted_log_HEX += part.ToString("X2"); } string queryString = "insert into logs values(\'" + labnumber + "\', " + encrypted_log_HEX + ", CURRENT_TIMESTAMP)"; Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection); if (command.Connection.State != System.Data.ConnectionState.Open) { command.Connection.Open(); } command.ExecuteNonQuery(); command.Connection.Close(); return(0); } catch { return(-1); } }
protected override void AfterUpdate(Indicador oldValue, Indicador newValue) { if (oldValue.Identificador != newValue.Identificador && oldValue.TipoCalculo == TipoCalculo.NaoCalculado) { Exception excSql = null; string termoPesquisa = $"[{oldValue.Identificador}]"; string novoIdentificador = $"[{newValue.Identificador}]"; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); string sqlUpdate = $"update Indicador set Formula = replace(Formula, '{termoPesquisa}', '{novoIdentificador}') where TipoCalculo <> 1 and Formula like '%{termoPesquisa}%'"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sqlUpdate, connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } } if (oldValue.Corporativo && !newValue.Corporativo) { var lista = projetoEstruturaOrganizacionalRepository.Filter(item => item.Tipo == TipoProjetoEstruturaOrganizacional.Corporativo && item.IdIndicador == newValue.Id); var listaIds = new List <long>(); lista?.ToList()?.ForEach(item => listaIds.Add(item.Id)); projetoEstruturaOrganizacionalRepository.DeleteMany(listaIds); } }
public static void GetPrivateKeyFromDB() { //getting key string queryString = "select * from keys where login_id=" + user_id.ToString(); var command_newkey = new Microsoft.Data.SqlClient.SqlCommand(queryString, database_connection); if (command_newkey.Connection.State != System.Data.ConnectionState.Open) { command_newkey.Connection.Open(); } command_newkey.ExecuteNonQuery(); var reader = command_newkey.ExecuteReader(); byte[] dbprivkey = new byte[48]; byte[] dbiv = new byte[32]; while (reader.Read()) { reader.GetBytes(1, 0, dbprivkey, 0, 48); reader.GetBytes(2, 0, dbiv, 0, 32); } AesInst = System.Security.Cryptography.Aes.Create(); var privKeysha256 = MathOperations.sha256_byte(userpassword); var ivmd5 = MathOperations.md5_byte(userpassword); //enc private key with sha256(pwd) and md5(pwd) AesInst.Key = privKeysha256; AesInst.IV = ivmd5; var SecretKey = AesInst.CreateDecryptor().TransformFinalBlock(dbprivkey, 0, dbprivkey.Length); var InitVector = AesInst.CreateDecryptor().TransformFinalBlock(dbiv, 0, dbiv.Length); //apply decrypted key AesInst.Key = SecretKey; AesInst.IV = InitVector; command_newkey.Connection.Close(); }
public void AvancarNivel(long id, long idSuperior) { CorrigirOrdens(idSuperior); var itens = Filter(item => item.IdSuperior == idSuperior).OrderBy(it => it.Ordem).ToList(); if (itens.Last().Id == id) { throw new Exception("Item não pode avançar."); } short ordem = itens.First(it => it.Id == id).Ordem; long idAfterElement = itens.First(it => it.Ordem == ordem + 1).Id; Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); string sql = $"update ProjetoEstruturaOrganizacional set Ordem = {ordem + 1} where Id = {id}; update ProjetoEstruturaOrganizacional set Ordem = {ordem} where Id = {idAfterElement};"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sql, connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } }
/// <summary> /// Attempt to create a DbParameter using the <see cref="Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand, string, object, bool?)"/> /// call for the specified column name. /// </summary> public static DbParameter TryCreateRelationalMappingParameter(string columnName, string parameterName, object value, TableInfo tableInfo) { if (columnName == null) { return(null); } if (!tableInfo.ColumnToPropertyDictionary.TryGetValue(columnName, out var propertyInfo)) { return(null); } try { var relationalTypeMapping = propertyInfo.GetRelationalTypeMapping(); using var dbCommand = new Microsoft.Data.SqlClient.SqlCommand(); return(relationalTypeMapping.CreateParameter(dbCommand, parameterName, value, propertyInfo.IsNullable)); } catch (Exception) { } return(null); }
public ImpotacaoLancamentosDto GerarLancamentos(IEnumerable <ImpotacaoLancamentos> lancamentos) { var builderErros = new System.Text.StringBuilder(); var builderStatus = new System.Text.StringBuilder(); var comandos = new List <Tuple <string, decimal, decimal, long?> >(); int numeroLinha = 0; foreach (var item in lancamentos) { numeroLinha++; var erros = new System.Text.StringBuilder(); var projeto = databaseContext.Projeto.FirstOrDefault(it => it.Id == item.IdProjeto); if (projeto is null) { erros.Append(" O ID de projeto não existe. "); } var dataAtual = DateTime.UtcNow.Date; if (!projeto.Ativo || projeto.DataInicio.Date > dataAtual || projeto.DataTermino < dataAtual) { erros.Append(" O projeto deve estar ativo e dentro da vigência atual. "); } var indicador = databaseContext.Indicador.FirstOrDefault(it => it.Identificador == item.Identificador); if (indicador is null) { erros.Append(" O identificador não existe. "); } if (erros.Length > 1) { builderErros.AppendLine($"A linha {numeroLinha} está com problema(s): '{erros.ToString()}'."); } else if (builderErros.Length == 0) { var lancamento = databaseContext.IndicadorLancamentos.FirstOrDefault(it => it.IdProjeto == item.IdProjeto && it.IdIndicador == indicador.Id && it.Ano == item.Ano && it.Mes == item.Mes); decimal valorMeta = 0.00m; decimal valorRealizado = 0.00m; string status = "Valor da meta e valor do realizado foram zerados porque o tipo de cálculo do indicador está definido como 'Meta e realizado calculados'."; if (indicador.TipoCalculo == TipoCalculo.NaoCalculado) { valorMeta = item.ValorMeta; valorRealizado = item.ValorRealizado; status = string.Empty; } else if (indicador.TipoCalculo == TipoCalculo.SomenteMeta) { valorRealizado = item.ValorRealizado; status = "Valor da meta foi zerado porque o tipo de cálculo do indicador está definido como 'Meta calculada'."; } else if (indicador.TipoCalculo == TipoCalculo.SomenteRealizado) { valorMeta = item.ValorMeta; status = "Valor do realizado foi zerado porque o tipo de cálculo do indicador está definido como 'Realizado calculado'."; } if (lancamento is null) { comandos.Add(new Tuple <string, decimal, decimal, long?>($"insert into IndicadorLancamento (IdProjeto, IdIndicador, Ano, Mes, ValorMeta, ValorRealizado) values ({item.IdProjeto}, {indicador.Id}, {item.Ano}, {item.Mes}, @p1, @p2);", valorMeta, valorRealizado, null)); builderStatus.Append($"<p style='color:green'>A linha {numeroLinha} foi <b>inserida</b> com sucesso. (<i style='color:orange'>{status}</i>)</p><br/>").Replace("(<i style='color:orange'></i>)", string.Empty); } else { comandos.Add(new Tuple <string, decimal, decimal, long?>("update IndicadorLancamento set ValorMeta = @p1, ValorRealizado = @p2 where Id = @p3;", valorMeta, valorRealizado, lancamento.Id)); builderStatus.Append($"<p style='color:blue'>A linha {numeroLinha} foi <b>atualizada</b> com sucesso. (<i style='color:orange'>{status}</i>)</p><br/>").Replace("(<i style='color:orange'></i>)", string.Empty); } } } if (builderErros.Length > 1) { return(new ImpotacaoLancamentosDto { Sucesso = false, Mensagem = builderErros.ToString().Replace(Environment.NewLine, "<br/>") }); } string erro = null; using var context = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); context.Open(); using var transaction = context.BeginTransaction(); foreach (var item in comandos) { using var cmd = new Microsoft.Data.SqlClient.SqlCommand(item.Item1, context, transaction); try { cmd.Parameters.AddWithValue("p1", item.Item2); cmd.Parameters.AddWithValue("p2", item.Item3); if (item.Item4.HasValue) { cmd.Parameters.AddWithValue("p3", item.Item4.Value); } cmd.ExecuteNonQuery(); } catch (Exception exc) { erro = exc.Message; break; } } try { if (erro is null) { transaction.Commit(); } else { transaction.Rollback(); } context.Close(); } catch { } if (erro != null) { return(new ImpotacaoLancamentosDto { Sucesso = false, Mensagem = erro }); } return(new ImpotacaoLancamentosDto { Sucesso = true, Mensagem = builderStatus.ToString() }); }
private void button1_Click(object sender, EventArgs e) { try { if (textBox_prevpwd.Text != MSSQL_logging.userpassword) { throw new Exception("Пароль не соответствует текущему"); } if (textBox_newpwd.TextLength < 14) { throw new Exception("Минимальная длина пароля - 14 символов"); } if (textBox_newpwd.Text != textBox_newPwdCopy.Text) { throw new Exception("Новые пароли не совпадают"); } //checking for bad passwords bool bad = false; foreach (var pwd in badpwdlist) { if (pwd == textBox_newpwd.Text) { bad = true; break; } } if (bad) { throw new Exception("Выбран слабый пароль!"); } //hashing string hash = MathOperations.sha256(textBox_newpwd.Text); //update password =============== string query = "update logins set hash='" + hash + "' where id=" + MSSQL_logging.user_id.ToString(); var command = new Microsoft.Data.SqlClient.SqlCommand(query, MSSQL_logging.database_connection); if (command.Connection.State != System.Data.ConnectionState.Open) { command.Connection.Open(); } command.ExecuteNonQuery(); //delete old pwd ============= query = "delete from keys where login_id=" + MSSQL_logging.user_id; command.CommandText = query; command.ExecuteNonQuery(); //renew key ================= MSSQL_logging.GetPrivateKeyFromDB(); var aesInst = System.Security.Cryptography.Aes.Create(); var privKeysha256 = MathOperations.sha256_byte(textBox_newpwd.Text); var ivmd5 = MathOperations.md5_byte(textBox_newpwd.Text); //enc private key with sha256(pwd) and md5(pwd) aesInst.Key = privKeysha256; aesInst.IV = ivmd5; var Encrypted_SK = aesInst.CreateEncryptor().TransformFinalBlock(MSSQL_logging.AesInst.Key, 0, MSSQL_logging.AesInst.Key.Length); string Encrypted_SK_String_HEX = "0x"; foreach (byte part in Encrypted_SK) { Encrypted_SK_String_HEX += part.ToString("X2"); } var Encrypted_IV = aesInst.CreateEncryptor().TransformFinalBlock(MSSQL_logging.AesInst.IV, 0, MSSQL_logging.AesInst.IV.Length); string Encrypted_IV_String_HEX = "0x"; foreach (byte part in Encrypted_IV) { Encrypted_IV_String_HEX += part.ToString("X2"); } query = "insert into keys values (" + MSSQL_logging.user_id.ToString() + ", " + Encrypted_SK_String_HEX + ", " + Encrypted_IV_String_HEX + ")"; command.CommandText = query; command.ExecuteNonQuery(); command.Connection.Close(); //renew obj================= MSSQL_logging.userpassword = textBox_newpwd.Text; MSSQL_logging.GetPrivateKeyFromDB(); MessageBox.Show("Успешно!", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public IList <RelatorioFiltroResultado> ObterLancamentosParaRelatorio(RelatorioFiltro filtro) { var builder = new System.Text.StringBuilder(); builder.AppendLine("select e.*, f.Nome, g.Nome, h.Nome from ( "); builder.AppendLine("select a.IdIndicador, a.IdSuperior, a.IdProjeto, b.Ano, b.Mes, c.Nome, c.Identificador, d.Sigla, c.ValorPercentualCriterio, c.ValorPercentualPeso, "); builder.AppendLine("b.ValorMeta, b.ValorRealizado, "); builder.AppendLine("(select IdUsuario from ProjetoEstruturaOrganizacional where Id = a.IdSuperior) Usuario, "); builder.AppendLine("(select IdNivelOrganizacional from ProjetoEstruturaOrganizacional where Id = (select IdSuperior from ProjetoEstruturaOrganizacional where Id = a.IdSuperior)) Cargo "); builder.AppendLine("from ProjetoEstruturaOrganizacional a "); builder.AppendLine("inner join IndicadorLancamento b on (a.IdIndicador = b.IdIndicador and a.IdProjeto = b.IdProjeto) "); builder.AppendLine("inner join Indicador c on (a.IdIndicador = c.Id) "); builder.AppendLine("inner join UnidadeMedida d on (c.IdUnidadeMedida = d.Id) "); builder.AppendLine("where a.Tipo = 7 "); builder.AppendLine($"and b.Ano >= {filtro.AnoInicial} and b.Ano <= {filtro.AnoFinal} "); builder.AppendLine($"and b.Mes >= {filtro.MesInicial} and b.Mes <= {filtro.MesFinal}) e "); builder.AppendLine("inner join Usuario f on (e.Usuario = f.Id) "); builder.AppendLine("inner join NivelOrganizacional g on (e.Cargo = g.Id) "); builder.AppendLine("inner join Projeto h on (e.IdProjeto = h.Id) "); builder.AppendLine("where 1 = 1 "); if (filtro.IdProjeto.HasValue) { builder.AppendLine($"and e.IdProjeto = {filtro.IdProjeto.Value} "); } if (filtro.IdIndicador.HasValue) { builder.AppendLine($"and e.IdIndicador = {filtro.IdIndicador.Value} "); } if (filtro.IdUsuario.HasValue) { builder.AppendLine($"and e.Usuario = {filtro.IdUsuario.Value} "); } if (filtro.IdCargo.HasValue) { builder.AppendLine($"and e.Cargo = {filtro.IdCargo.Value} "); } using var context = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); context.Open(); using var command = new Microsoft.Data.SqlClient.SqlCommand(builder.ToString(), context); using var reader = command.ExecuteReader(); var result = new List <RelatorioFiltroResultado>(); while (reader.Read()) { result.Add(new RelatorioFiltroResultado { IdIndicador = reader.GetInt64(0), IdSuperior = reader.GetInt64(1), IdProjeto = reader.GetInt64(2), Ano = reader.GetInt32(3), Mes = reader.GetInt32(4), NomeIndicador = reader.GetString(5), Identificador = reader.GetString(6), UnidadeMedida = reader.GetString(7), ValorPercentualCriterio = reader.GetDecimal(8), ValorPercentualPeso = reader.GetDecimal(9), ValorMeta = reader.GetDecimal(10), ValorRealizado = reader.GetDecimal(11), IdUsuario = reader.GetInt64(12), IdCargo = reader.GetInt64(13), NomeUsuario = reader.GetString(14), NomeCargo = reader.GetString(15), NomeProjeto = reader.GetString(16) }); } try { context.Close(); } catch { } return(result); }
public async Task <IActionResult> registerStudent(StdEnr input) { SqlConnection conn = new SqlConnection(_connectionString); SqlCommand com = new SqlCommand(); Study study; { com.Connection = conn; com.CommandText = "select IdStudy, Name from Studies where Name = @name"; com.Parameters.AddWithValue("name", input.Studies); conn.Open(); try { SqlDataReader dataReader = await com.ExecuteReaderAsync(); await dataReader.ReadAsync(); study = new Study { IdStudy = int.Parse(dataReader["IdStudy"].ToString()), Name = dataReader["Name"].ToString() }; if (study == null) { return(BadRequest()); } } catch { return(null); } } conn = new SqlConnection(_connectionString); com = new SqlCommand(); conn.Open(); SqlTransaction transaction = conn.BeginTransaction(); com.Connection = conn; com.Transaction = transaction; com.CommandText = @"declare @enrollmentId int select @enrollmentId = e.IdEnrollment from Enrollment e left join Studies s on e.IdStudy = s.IdStudy where e.IdStudy = @studyId and e.Semester = 1 if @enrollmentId is null begin select @enrollmentId = max(IdEnrollment) + 1 from Enrollment insert into Enrollment values (@enrollmentId, 1, @studyId, getdate()); end insert into Student values (@index, @firstName, @lastName, @birthDate, @enrollmentId)"; com.Parameters.AddWithValue("studyId", study.IdStudy); com.Parameters.AddWithValue("index", input.IndexNumber); com.Parameters.AddWithValue("firstName", input.FirstName); com.Parameters.AddWithValue("lastName", input.LastName); com.Parameters.AddWithValue("birthDate", input.BirthDate); try { await com.ExecuteNonQueryAsync(); await transaction.CommitAsync(); } catch (Exception e) { await transaction.RollbackAsync(); } return(StatusCode(200)); }
//--------------------------------------------------------------------- /// <summary> /// Extension to Microsoft.Data.SqlClient to load query with untrusted /// data provided in args parameters safely to mitigate the risk from /// SQL injection attacks /// </summary> /// <param name="cmd"></param> /// <param name="queryText"></param> /// <param name="queryTextArgs"></param> /// <exception cref="IronBox.AntiSQLi.Models.AntiSQLiException"> /// Thrown on loading errors /// </exception> //--------------------------------------------------------------------- public static void LoadQuerySecure(this Microsoft.Data.SqlClient.SqlCommand sqlCommandObj, String queryText, params Object[] queryTextArgs) { AntiSQLiCommon.ParameterizeAndLoadQuery <Microsoft.Data.SqlClient.SqlParameter>(sqlCommandObj, queryText, queryTextArgs); }
private void button1_Click(object sender, EventArgs e) { try { string host = textBox_host.Text; string port = textBox_port.Text; //resolving host string string hostString = "tcp:" + host; if (port != "Default" && port != "") { hostString += ", " + port; } string getLoginsLogin = "******"; string getLoginsPassw = "ceb3478&Bc23b2&"; Microsoft.Data.SqlClient.SqlConnectionStringBuilder extractLoginsConnBuilder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(); extractLoginsConnBuilder.DataSource = hostString; extractLoginsConnBuilder.ConnectTimeout = 10; extractLoginsConnBuilder.UserID = getLoginsLogin; extractLoginsConnBuilder.Password = getLoginsPassw; extractLoginsConnBuilder.Authentication = Microsoft.Data.SqlClient.SqlAuthenticationMethod.SqlPassword; extractLoginsConnBuilder.IntegratedSecurity = false; extractLoginsConnBuilder.TrustServerCertificate = true; string queryString = "select * from logins"; StringBuilder errorMessages = new StringBuilder(); int user_id = 0; using (Microsoft.Data.SqlClient.SqlConnection connection1 = new Microsoft.Data.SqlClient.SqlConnection(extractLoginsConnBuilder.ConnectionString)) { Microsoft.Data.SqlClient.SqlCommand command1 = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection1); try { command1.Connection.Open(); command1.ExecuteNonQuery(); var reader = command1.ExecuteReader(); if (!reader.HasRows) { throw new Exception("Provided login not found or password is incorrect"); } string login = textBox_login.Text; string password = textBox_password.Text; string hash = MathOperations.sha256(password); bool login_in = false; while (reader.Read()) { if (reader.GetString(1) == login && reader.GetString(2) == hash) { user_id = reader.GetInt32(0); login_in = true; } } reader.Close(); command1.Connection.Close(); if (!login_in) { throw new Exception("Provided login not found or password is incorrect"); } } catch (Microsoft.Data.SqlClient.SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } throw new Exception(errorMessages.ToString()); } } string basicLogin = "******"; string basicPassw = "n3i7A7834bo&T21h@tbn"; extractLoginsConnBuilder.UserID = basicLogin; extractLoginsConnBuilder.Password = basicPassw; queryString = "select * from keys"; Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(extractLoginsConnBuilder.ConnectionString); Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection); try { command.Connection.Open(); command.ExecuteNonQuery(); //check if user has key var reader = command.ExecuteReader(); bool has_key = false; while (reader.Read()) { if (reader.GetInt32(0) == user_id) { has_key = true; } } reader.Close(); if (!has_key) { MessageBox.Show("Ключ не найден. Сейчас будет сгенерирован новый ключ и добавлен в базу данных.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning); var aesInst = System.Security.Cryptography.Aes.Create(); var privkey = aesInst.Key; var iv = aesInst.IV; var privKeysha256 = MathOperations.sha256_byte(textBox_password.Text); var ivmd5 = MathOperations.md5_byte(textBox_password.Text); //enc private key with sha256(pwd) and md5(pwd) aesInst.Key = privKeysha256; aesInst.IV = ivmd5; var Encrypted_SK = aesInst.CreateEncryptor().TransformFinalBlock(privkey, 0, privkey.Length); string Encrypted_SK_String_HEX = "0x"; foreach (byte part in Encrypted_SK) { Encrypted_SK_String_HEX += part.ToString("X2"); } var Encrypted_IV = aesInst.CreateEncryptor().TransformFinalBlock(iv, 0, iv.Length); string Encrypted_IV_String_HEX = "0x"; foreach (byte part in Encrypted_IV) { Encrypted_IV_String_HEX += part.ToString("X2"); } queryString = "insert into keys values (" + user_id.ToString() + ", " + Encrypted_SK_String_HEX + ", " + Encrypted_IV_String_HEX + ")"; Microsoft.Data.SqlClient.SqlCommand command_newkey = new Microsoft.Data.SqlClient.SqlCommand(queryString, connection); //command.Connection.Open(); command_newkey.ExecuteNonQuery(); command_newkey.Connection.Close(); } //save session data MSSQL_logging.user_id = user_id; MSSQL_logging.userpassword = textBox_password.Text; MSSQL_logging.database_connection = connection; MSSQL_logging.GetPrivateKeyFromDB(); command.Connection.Close(); //open start panel this.Hide(); var form_start = new Form_start(); form_start.Closed += (s, args) => this.Close(); form_start.Show(); } catch (Microsoft.Data.SqlClient.SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } throw new Exception(errorMessages.ToString()); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } }