MySQL Query Returns Parameter Column Name

Question

I am working in C# and MySQl in VS2015 to query my database and return a the information in a VARCHAR type column titled "method". However, the query returns the string "method", and not the values of the method column.

below is the code:

       string queryOne = "SELECT " + "@columnName" + " FROM log.transactions";

        MySqlCommand cmdOne = new MySqlCommand(queryOne, connectionString);

        cmdOne.Parameters.AddWithValue("@columnName", "method");

        MySqlDataReader dataReaderOne = cmdOne.ExecuteReader();
        while (dataReaderOne.Read())
        {
            Console.WriteLine(dataReaderOne.GetString(0));
        }
        dataReaderOne.Close();

While this is the output:

        method
        method
        method
          .
          .
          .

.. for the number of rows in the method column. Is this a formatting problem? Is it possible that the configuration of my database is preventing VarChar's from returning correctly? When I change the query to query a column of type INT, it returns the correct values for an INT type column.

15ee8f99-57ff-4f92-890c-b56153 · Answer 1 · 2016-10-13T17:48:17.617

You can't parameterize a column name in a select statment. What you're doing is exaclty like saying select 'foo' from log.transactions. It selects the string 'foo' once for each row. You're just sticking a string value in there; it's not parsing the string value as SQL.

What you can do (if you can afford it) is select * from log.transactions, then your C# code can grab the data in whatever column the caller passed you the name of. With a lot of rows you could be dragging a lot of useless junk back from the DB though.

What you want in the code you show, though is just this:

string queryOne = "SELECT method FROM log.transactions";

If you really want to parameterize "method", that's sketchy because of SQL injection vulnerabilities.

string queryOne = "SELECT " + fieldname + " FROM log.transactions";

That looks good until some comedian using your application gives you a value of "0; drop table log.transactions;--" in the textbox. Then you've got troubles. If you ever concatenate a string variable into a SQL string that you're going to execute, you've got to be fanatical about sanitizing it, and even then you want to avoid it any way you can. It's Russian roulette.

Awesome, thank you for letting me know! – KM2001 Oct 13 '16 at 17:46 — KM2001, Oct 13 '16 at 17:46

score 1 · Answer 2 · answered Oct 13 '16 at 17:43

Your query formation has to be like if you want to keep your column dynamic.Now pass column name accordingly.

string queryOne = "SELECT " + column_name + " FROM log.transactions";

MySqlCommand cmdOne = new MySqlCommand(queryOne, connectionString);        
MySqlDataReader dataReaderOne = cmdOne.ExecuteReader();
while (dataReaderOne.Read())
    {
        Console.WriteLine(dataReaderOne[column_name]);
    }
dataReaderOne.Close();

MySQL Query Returns Parameter Column Name

2 Answers2