1

I have a class, ValuesField, which manages values for ComboBoxes.

The constructor specifies a database table from which the values can be retrieved, along with two fields from which to SELECT data. 

public ValuesField(string databaseTable, string idField, string valueField)
{
    this.databaseTable = databaseTable;
    this.idField = idField;
    this.valueField = valueField; 
}

My method, GetValues() retrieves this data (from which a ComboBox can then be populated). I was constructing the CommandText as a simple string, however I wanted to use a parameterized query for safety.

The simple string command - 

dbCommand.CommandText = "SELECT " + idField + "," + valueField + " FROM " + 
    databaseTable + " ORDER BY " + valueField;

The parameterized query - 

dbCommand.CommandText = "SELECT @idField, @valueField FROM @databaseTable 
    ORDER BY @valueField";

dbCommand.Parameters.AddWithValue("@idField", idField);
dbCommand.Parameters.AddWithValue("@valueField", valueField);
dbCommand.Parameters.AddWithValue("@databaseTable", databaseTable); 

dbReader = dbCommand.ExecuteReader();

The parameterized query throws a MySqlException on ExecuteReader() with the Message 'You have an error in your SQL syntax'.

  • I checked the value of the CommandText at the point the Exception is thrown and it (using the Watch) and the CommandText still shows as "SELECT @idField, @valueField FROM @databaseTable ORDER BY @valueField" - so I am not sure how to examine the syntax for any obvious errors as I would normally do.

  • I see that this is apparently not possible according to this answer.

Is there a way to view the actual CommandText with the values included in order to diagnose syntax errors?

Is there a safe alternative to specify a table name, if indeed using a parameterized query is not possible?

Community
  • 1
  • 1
Eilidh
  • 1,270
  • 1
  • 13
  • 33
  • You can create enumerator, your comboboxes should return id of that concrete enum and based on the value, you should put a table name into query. It is safe to sql injects. – libik Oct 29 '14 at 14:57
  • 1
    I'm missing something here. If the SQL command is a simple select command to populate a combo box, where is the security concern? Parametrized queries are to avoid SQL injection attacks. If there is no user input in the SQL command to begin with, then why all the fuss? Apart from that, AFAIK you can not parametrize table names; having non trusted users specifying table names doesn't seem like a good idea to begin with. – InBetween Oct 29 '14 at 15:04
  • I don't think you're missing anything, I think I'm just doing things in a very illogical way... :) I decided to look at parameterized queries because I am uploading to my database including user-entry from TextBoxes. I just decided to try a parameterized query on another piece of code, though - this is mostly just a point of interest and an additional (as you say, probably unnecessary!) layer of safety :) – Eilidh Oct 29 '14 at 15:14

1 Answers1

0

Try creating a generic table for your combo boxes: [id, value, combo] and a add the combo metadata to other tables.

Or just use a repository pattern :)

Juan
  • 3,675
  • 20
  • 34