CODEWITHSUNDEEP
c#sqloracle-databaseparameterssql-injection
FistiPaul
FistiPaul

Reputation: 53

Query doesn't add parameters' value when executed

I want to insert multiple rows into my database, I tried to use parameters but I did something wrong.

In debug mode, the parameters do have values, but once the insert is done, I still have @compte, @mo, @userimp in my database instead of their values.

string INSERTDELAMORTPart2 = "'123'," + "'@compte'" + "," + "'@mo'" + "," + "'ubs'" + "," + "'2'" + "," + "'@userimp'" + "," + "'22.10.17'" + ",'";

OdbcCommand Query = new OdbcCommand(INSERTDELAMORTPart2, InfoConnexion); 

Query.Parameters.AddWithValue("@compte", _cb_Compte.SelectedItem);
Query.Parameters.AddWithValue("@mo", MondantResultat);
Query.Parameters.AddWithValue("@userimp", UserImp);

Query.ExecuteNonQuery();

I have tried Parameters.Add, and also Parameters["@name"].Value, but the result stays the same.

What am I doing wrong ?

EDIT :

I have an Oracle database and I need the single quote to insert my values. I have tried to pass my whole INSERT string into a parameter but it doesn't work.

I'm about to give up and just replace the ' by '' even if it's a bad idea.

Upvotes: 1

Views: 749

Answers (3)

Hambone
Hambone

Reputation: 16377

A few things.

  1. If you are using Oracle, I highly recommend you use ODP.net or managed ODP.net, not ODBC. ODBC should work, but the native drivers will work more efficiently and will avoid any oddities you might with with the abstraction layer ODBC provides. Managed ODP.net has the additional advantage of not requiring an Oracle client be installed on the target machine

  2. SQL Server (and other databases) use the "@" as a parameter, but Oracle uses the colon ":" instead. Some databases (like PostgreSQL) will take either. While the parameter in your SQL requires the colon, when you use assign the parameter in .NET you need to omit it. Again, this is an Oracle thing you won't find in other databases.

  3. Your SQL doesn't appear to have any command in it... only values. Where is your "insert into?"

  4. Especially with Oracle, you want to be explicit about your datatypes... so use Add instead of AddWithValue (which is actually an overload of Add in ODP.net). Alternatively, you can set the datatype later.

The converted code would look something like this:

string insert = "insert into my_table values (:COMPTE, :MO, :USERIMP)";

using (OracleCommand cmd = new OracleCommand(insert, Connection))
{
    // no ":" in the parameter declarations
    cmd.Parameters.Add(new OracleParameter("COMPTE", OracleDbType.Int32));
    cmd.Parameters.Add(new OracleParameter("MO", OracleDbType.Varchar2));
    cmd.Parameters.Add(new OracleParameter("USERIMP", OracleDbType.Varchar2));
    cmd.Parameters[0].Value = 123;
    cmd.Parameters[1].Value = "ubs";
    cmd.Parameters[2].Value = "22.10.17";

    cmd.ExecuteNonQuery();
}

I guessed at your datatypes -- make sure they match your DDL when you actually implement this, and if these aren't the only three columns, update your insert command appropriately.

If for some reason I misunderstood, and this is not Oracle, I still recommend use of the native driver rather than ODBC. When you use ODBC, you assume a lot about the target machine -- assumptions that may not turn out to be true.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

You have this:

string INSERTDELAMORTPart2 = "'123'," + "'@compte'" ...

Note the single quotes around the parameter names. They should not be there. You want this instead:

string INSERTDELAMORTPart2 = "'123'," + "@compte"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The command should look something like this:

insert into t(compte, mo, userimp) -- of course the table and columns should be the right names
    values (@compte, @mo, @userimp);

No single quotes are needed.

I would recommend against using AddWithValue(). Instead, be explicit about the types.

Upvotes: 2

Related Questions