CODEWITHSUNDEEP
c#mysqlsyntax
Jill
Jill

Reputation: 97

C# and MySQL - Incorrect Syntax?

Suppose I this method from my MySQLOperations class:

    public bool checkIfRowExists(string tableName, string columnToLookIn, string dataToLookFor)
    {
        string myConnectionString = "Server = " + server + "; Database = " + dbName + "; UID = " + user + "; Password = " + password + ";";
        int isExisting = 0;
        using (MySqlConnection myConnection = new MySqlConnection(myConnectionString))
        {
            using (MySqlCommand myCommand = new MySqlCommand("SELECT EXISTS (SELECT 1 FROM @tablename WHERE @columnname = @data);", myConnection))
            {
                myCommand.Parameters.AddWithValue("@tablename", tableName);
                myCommand.Parameters.AddWithValue("@columnname", columnToLookIn);
                myCommand.Parameters.AddWithValue("@data", dataToLookFor);
                try
                {
                    myConnection.Open();
                    isExisting = (int)myCommand.ExecuteScalar();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    myConnection.Close();
                    MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                return (isExisting == 1);
            }
        }
    }

And in another class, I created a MySQLOperations object. I called on this method within an if statement: if(objSQLOperations.checkIfRowExists("tblInventory", "ItemID", txtItemID.Text)). Let us assume that txtItemID contains a valid 10-digit number. And that in my database, I have a table named tblInventory with a column ItemID.

My problem is that the statements within my catch block executes, saying there was an error with my SQL syntax. "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 ''tblInventory' WHERE 'ItemID' = '11111111111'' at line 1." is popped at a MessageBox when my txtItemID contains the text "1111111111".

I believe my SELECT EXISTS statement is correct.

Upvotes: 0

Views: 150

Answers (1)

shf301
shf301

Reputation: 31394

No you can't use parameters for table or column names. You'll have to build up the SQL string dynamically by concatenation.

Like:

var sql = "SELECT EXISTS(SELECT 1 FROM " + tablename + 
           " WHERE " + columnNameToLookIn + " = @data);"
using (MySqlCommand myCommand = new MySqlCommand(sql, myConnection))

This of course opens you up to SQL injection if tableName and columnNameToLookIn can be user entered. However if the tableName and columnNameToLookIn are only set in code as it appears in your example then it is not a problem.

Related:

use a variable for table name in mysql sproc

Dynamic table names in stored procedure function

Upvotes: 1

Related Questions