0

I needed to run this query:

"Select column_name from all_tab_columns 
where table_name=''" + tableName + " owner='" + ownerName + "'";

This is running it as an OracleCommand in C#. It didn't work. I tried many variations including different variables but it never works when I put table_name in the where clause. It's not because I'm not using LIKE either.

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
Amit Toren
  • 351
  • 3
  • 13

1 Answers1

1

A properly formatted command text would have to look something like this:

var query = String.Format(@"
    SELECT 
        column_name 
    FROM all_tab_columns 
    WHERE table_name='{0}'
    AND owner='{1}'", tableName, ownerName);

This is vulnerable to injection attacks if tableName and ownerName were user input. In this case, use a parametrized command instead:

var query = @"
    SELECT 
        column_name 
    FROM all_tab_columns 
    WHERE table_name=:tablename'
    AND owner=:ownername";

The values are then assigned using OracleParameter instances.

Community
  • 1
  • 1
Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
  • I know that my query isn't correctly formatted. It was just for testing it. What I'm saying is that this query, no matter the format, will never work when there's the tablename in the where clause... – Amit Toren Feb 23 '17 at 07:34
  • `table_name` is a valid column name in the `all_tab_columns` table. my answer is not about the "nicer formatting", but for example in your original code you had a doubled ' quote which would cause a syntax error. so when you say "never work", what do you get? an empty resultset? an exception? ORA-00933: SQL command not properly ended? – Cee McSharpface Feb 23 '17 at 11:03
  • The double single quote is probably a typo. I didn't copy paste it. – Amit Toren Feb 23 '17 at 17:47
  • I get a OracleDataReader with the flag HasRows false. So an empty resultset. Sorry – Amit Toren Feb 23 '17 at 17:48
  • in this case, it is no longer a coding problem. focus on the values of the parameters and the actual row/column values in that table. maybe it is a case sensitivity issue? – Cee McSharpface Feb 27 '17 at 10:28