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?

Parameters cannot be used to specify table or column names. By the way the SQL syntax is DELETE FROM _tablename_ — Steve, Dec 30 '16 at 20:13
ok, so how do I do this to prevent SQL injection? DELETE works in SqlDeveloper — samneric, Dec 30 '16 at 20:14
You don't let your user type the table name. You need to present a list of tables and let him/her select the table name — Steve, Dec 30 '16 at 20:15
yup - that's what I do but those table names are passed back in string format. a hacker could change those string values. sounds like I need to add some extra validation based on a list of valid table names. thanks — samneric, Dec 30 '16 at 20:17

score 1 · Accepted Answer · answered Jan 01 '17 at 02:35

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.

Cheers. That's a good way of avoiding injection. I was actually using EF so I used reflection to make sure the table existed in the edmx model :) — samneric, Jan 03 '17 at 15:48

Execute SQL command with parameter using Devart EF Drivers for Oracle

1 Answers1