how to insert a select command into the BD using c#?

Question

I wanted to have a table of select commands in the bank, and they were read in a combobox. I have a textbox where I write the command to be executed by the bank, a button for it and a combobox with recorded commands. When the command I want to execute is not in the combobox, I type. And I wanted it to be saved to be read in the combobox if it didn’t exist. I did so:

public OracleDataReader VerificarComandoExistente(string descricao)
        {
         strQuery = "select * from comando where comando = '" + descricao + "' ";
            return ObjBancoDados.RetornaDataReader(strQuery);
        }

In C# it does not accept this way when I put a command with Where where it has to have the quotes. Ex select * from all_tables Where Owner ='hr' . Does not read hr.

In the bank I made the test, exchanging '' for "". Ran normal in sqldeveloper, but not in c#.

Summarizing. The system has to receive the code, check in the bank if it exists, and save in the table if it does not exist.

Look I understood what is happening. I make a simple select and the result goes to datagridview:

" select * from all_tables Where Owner = 'HR' "

After you have done this if this command is not in the bank, it will need to be saved. Hence a different select:

" select command from command Where command ='select * from all_tables Where Owner ='HR' ' "

The second select runs after the first

The problem is that single quotes work in the first command and in the second command have to be double quotes for Oracle to recognize (works directly in Oracle) It would have to be something like the Replace method, only I don’t think you can quote...

None of the solutions worked

Answer 1

I could try to give an answer that just solves your problem, but they’ve already done that in the other answer. Instead I’ll pass you off as you should be doing.

Your code is unsafe, do not do your database commands in any way. They will be extremely vulnerable.

You did not inform which adapter you are using to connect to the bank so I will start by giving an example with the Oracle.Manageddataaccess which is the most advisable for the Oracle in my opinion, but which can also be used with the Oracle.DataAccess.Client:

strQuery = "select * from comando where comando = :MeuParametro";
OracleCommand oraCommand = new OracleCommand(strQuery, db);
oraCommand.Parameters.Add(new OracleParameter("MeuParametro", varParametro));

This is the right way to pass parameters to your string. Consider changing your code.

In addition to avoiding your original problem of need to quote or not, you will have a secure code.

Below is an example of a complete method:

public MeuComando VerificarComandoExistente(string comando)
{
    //Esse é apenas um objeto de exemplo que uso pra retornar os dados
    MeuComando objMeuComando = new MeuComando();

    string connectionString = "Data Source=orcl;User Id=user;Password=pwd;";
    string sql = @"select * from comando where comando = :MeuComando";
    using (OracleConnection conn = new OracleConnection(connectionString))
    using (OracleCommand cmd = new OracleCommand(sql, conn))
    {
        conn.Open();
        cmd.Parameters.Add(":MeuComando", comando);
        OracleDataReader oraReader = cmd.ExecuteReader();

        //Utiliza aqui o if caso seja apenas uma linha de retorno...
        while (oraReader.Read())
        {
            objMeuComando = new MeuComando
            {//Aqui é onde vai preecher os dados retornados do BD para retornar no seu método.
                coluna1 = oraReader.GetInt32(0),
                coluna2 = oraReader.GetString(1),
                coluna3 = oraReader.GetString(2)
            };
        }
        return objMeuComando;
    }
}

And in case you want to understand more about that subject I have separated questions right here from Sopt on that subject:

1. Why parameterized SQL queries (name = ?) prevent SQL Injection?
2. How an SQL Injection Happens?

Answer 2

Depending on the C# version you are using you can use it like this

public OracleDataReader VerificarComandoExistente(string descricao)
    {
     strQuery = $"select * from comando where comando = {descricao}";
        return ObjBancoDados.RetornaDataReader(strQuery);
    }

You can use it like that too

  public OracleDataReader VerificarComandoExistente(string descricao)
    {
     strQuery = string.format("select * from comando where comando = {0}",descricao);
        return ObjBancoDados.RetornaDataReader(strQuery);
    }

Answer 3

I think George Wurthmann’s answer is more correct, but if you just want to know the syntax, have you tried to remove " and ' from the concatenation? In c# you can just call the variable at the end of a string.

Ex: strQuery = "... command = " + Description;

  public OracleDataReader VerificarComandoExistente(string descricao) {
          strQuery = "select * from comando where comando = " + descricao;
             return ObjBancoDados.RetornaDataReader(strQuery);
         }