Execute SQL command with parameter using Devart EF Drivers for Oracle

Question

I am trying to delete records from an Oracle table before inserting new ones using a sql command and a parameter because the value comes from the browser.

This is the code:

var tableName = "<myTableName>";
context.Database.ExecuteSqlCommand("DELETE :p0", tableName);

Oracle is throwing "ORA-00903: invalid table name".

I have also tried:

context.Database.ExecuteSqlCommand("DELETE :p0", new OracleParameter("p0", OracleDbType.VarChar, 200, tableName, ParameterDirection.Input)

Is there something simple I am missing?

Answer 1

If you bounce the table against ALL_TABLES you should be able to prevent any SQL Injection attacks:

private bool TruncateTable(string Schema, string Table)
{
    OracleCommand cmd = new OracleCommand("select count (*) from all_tables " +
        "where owner = :SCHEMANAME and table_name = :TABLENAME", conn);
    cmd.Parameters.Add("SCHEMANAME", Schema.ToUpper());
    cmd.Parameters.Add("TABLENAME", Table.ToUpper());

    if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
        return false;

    cmd.CommandText = string.Format("delete from {0}.{1}", Schema, Table);
    cmd.Parameters.Clear();
    cmd.ExecuteNonQuery();

    return true;
}

On DevArt, I think the Add would instead be AddWithValues but would otherwise look the same.

In this case, a return value of false meant there was no such table. This all presupposes the user has the ability to delete from the table in question.

Also, if possible, a truncate is a nice alternative to a delete from. It's quite a bit faster and resets the high water mark. I think you need to be an owner or have the "drop any table" privilege to do this, but there are other ways around it -- for example, have the DBA set up a stored procedure to do the truncation on certain tables.