0

My task is, I am trying to connect to a mysql DB and find the record count of the table.

First, I am making a connection to the DB and finding the list of tables in to a list object. Then iterating through the table list and finding the record count of each table. There are about 80 tables in the database among which 4 to 5 table's name starts with "blacklist_".

The record count is correctly getting returned for the tables except for the table name starting with "blacklist_". Getting the below error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?blacklist?_card' at line 1

I tried various ways of passing the table names.. Prepending with @, ? etc. I suspect if this is getting recognized as a keyword / reserved word because I see a '?' symbol on both side of this word where as I am passing the table name "blacklist_card" as a whole.

Is it possible that it is matching some REGEX pattern. ? Because I see ? symbol on either side of the word blacklist which is not something that I passed.

The table name appears proper in the list of table when I debug in VS 2017. The problem happens only when I query the table with this name. 

foreach (string table in tableList)
{
        using (DC1con)
        {
            if (DC1con.State != ConnectionState.Open)
            {
                DC1con.Open();
            }
            // MySqlCommand DC1cmd = new MySqlCommand("select count(1) from `" + table + "`;", DC1con);
            MySqlCommand DC1cmd = new MySqlCommand("select count(1) from '@table_name'", DC1con);
            DC1cmd.Prepare();
            DC1cmd.Parameters.AddWithValue("@table_name", table);
            DC1cmd.Parameters["@table_name"].Value = table;
            DC1cmd.CommandTimeout = 20000;
            MessageBox.Show(DC1cmd.CommandText.ToString());
            int DC1Count = Convert.ToInt32(DC1cmd.ExecuteScalar());
            DC1con.Close();
            DC1con.Dispose();
        }
}

The expectation is all the table record count must be returned. But getting attached error those tables where the name starts with "blacklist_".

Sudharsan Simhan
  • 21
  • 8
  • You can no have table name as parameter in this case. You should use string joining for this like `new MySqlCommand("select count(1) from ``" + table + "``", DC1con);` – Chetan May 13 '19 at 12:54
  • Yes.. that's how i had it earlier. Since it threw the same issue, i switched to this approach. Also, what I have done is more cleaner approach. Is it not ?? Please correct if i am wrong. – Sudharsan Simhan May 13 '19 at 12:57
  • Parameters in Command is used to pass the parameter in where clause. They can not be used to decide the table name dynamically. It is cleaner approach for query parameters not for table names or column names. It simply won't work. What problem did you face with the approach I suggested? – Chetan May 13 '19 at 13:00
  • The same error that i have mentioned in my question.."You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?blacklist?_card' at line 1".... However both approach works fine for other tables. Only the table name starting with "blacklist_" is having trouble. – Sudharsan Simhan May 13 '19 at 13:06
  • What is the exact table name here? – Chetan May 13 '19 at 13:22
  • blacklist_card,blacklist_ip,blacklist_mobile,blacklist_terminalid - These are few of the table names. – Sudharsan Simhan May 13 '19 at 13:32

3 Answers3

0

I don't know much but can't you use a string like :

String query = "select count(1) from ?";
MySqlCommand DC1cmd = new MySqlCommand(query, DC1con);

Then 

DC1cmd.Parameter(1,'@table_name');

Or something like that ? sorry I'm not much of an help.

Yorikiri
  • 39
  • 1
  • 4
0

As far as I know, "blacklist" is not a reserved keyword in MySQL. However, you cannot pass table names as parameters. See this question for further information. You must resort to string concatenation:

    MySqlCommand DC1cmd = new MySqlCommand("select count(1) from `" + table + "`;", DC1con);
    DC1cmd.CommandTimeout = 20000;
    MessageBox.Show(DC1cmd.CommandText.ToString());
    int DC1Count = Convert.ToInt32(DC1cmd.ExecuteScalar());
    DC1con.Close();
    DC1con.Dispose();

I removed Prepare() as well because it's useless without parameters and might mess up with the text of the query.

StackLloyd
  • 409
  • 2
  • 9
  • Thanks.... The below code block from the link you have provided sounds promising. I will check that out and get back to you. "SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(); string tbName = cmdBuilder.QuoteIdentifier(tableName);" .. Btw, as you suggest is how my code was at the beginning. But since this problem peeped in, I tried passing it as parameter. If mine is wrong, then i am more curious that how this is working for other table name ?? Any approach, works fine for other tables. and approaches i tried, having same problem with the table name starting with blacklist_ – Sudharsan Simhan May 13 '19 at 18:01
  • If you run this query "SELECT COUNT(1) FROM blacklist_card" directly from WorkBench... does it work fine? – Chetan May 14 '19 at 03:01
  • Please try doing what @ChetanRanpariya suggested and tell us the results. Also, what string does the `MessageBox.Show(DC1cmd.CommandText.ToString());` call show? (anyway, `CommandText` already is a string, as far as I know) – StackLloyd May 14 '19 at 09:01
  • SELECT COUNT(1) FROM blacklist_card" - On running this directly from Workbench, it returns the record count properly. – Sudharsan Simhan May 14 '19 at 12:44
  • @StackLloyd - Because of the size.. I am pasting as 4 comments. The first line is the query used. Second line is the content of the message box. Third line is the popup/exception. – Sudharsan Simhan May 14 '19 at 13:08
  • MySqlCommand("select count(1) from '?table_name'", DC1con); select count(1) from '?table_name' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''?table_name'' at line 1 – Sudharsan Simhan May 14 '19 at 13:09
  • MySqlCommand("select count(1) from '@table_name'", DC1con); select count(1) from '@table_name' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''@table_name'' at line 1 – Sudharsan Simhan May 14 '19 at 13:09
  • MySqlCommand("select count(1) from @table_name", DC1con); select count(1) from @table_name You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''?blacklist?_card'' at line 1 – Sudharsan Simhan May 14 '19 at 13:09
  • MySqlCommand("select count(1) from `" + table + "`;", DC1con); select count(1) from `​blacklist​_card`; Table 'cxpsadm.?blacklist?_card' doesn't exist – Sudharsan Simhan May 14 '19 at 13:09
  • One more observation is, if i hard code the table name, i guess it is working fine. The count returns 0 records successfully. But unfortunately all the blacklist_* tables are having no records. So i guess the 0 count is correct. – Sudharsan Simhan May 14 '19 at 13:42
  • @ChetanRanpariya : Also, it works fine if i hard code this table name in c# code - command string. – Sudharsan Simhan May 14 '19 at 14:09
  • If it works by hardcoding the table name, then the problem might reside in the `table` variable you pass in the concatenation. Are you sure `tableList` has the correct names of your tables? – StackLloyd May 14 '19 at 14:32
  • Yes @StackLloyd.. You are right.. There was a special char in the table name that was created in MySQL !!! – Sudharsan Simhan May 14 '19 at 14:54
  • Aaaah, yes! Finally we figured it out! Have you solved that already? – StackLloyd May 14 '19 at 15:09
0

At last the bug is out... There was an invisible char hidden before and after the word blacklist. I am yet to fix it and run my code. But I am pretty sure this is the problem. Below was the char that was troubling me for the past 2 days. The char between the two double quote was the culprit. 

U+200B    Zero-Width Space       ​      "​"
Sudharsan Simhan
  • 21
  • 8