Presence of @whatever in SQL command string gives me a fatal error

Question

So, presence of @whatever in SQL command string gives me a fatal error(Fatal error encountered during command execution.) but not when I execute the same query in MySQL Query Browser.

This is the simple example where I give a row number for every John in my table and show them in datagridview:

private void button1_Click(object sender, EventArgs e)
{
   _dataTable.Clear();

   try
   {
      _conn = new MySqlConnection(Cs);
      _conn.Open();

      string name = "john";
      //This is the string that gives me an error
      string ConnString = "SELECT name,lastname,(@Row := @Row + 1) AS RowNumber FROM person JOIN (SELECT @Row := 0) r WHERE name = '"+name+"'";

      //This one doesn't gives an error
      //string ConnString = "SELECT name,lastname FROM person WHERE name = '" + name + "'";

      _cmd = new MySqlCommand
      {
         Connection = _conn,
         CommandText = ConnString
      };

      _cmd.ExecuteNonQuery();

      _da = new MySqlDataAdapter(_cmd);
      _da.Fill(_dataTable);

      _cb = new MySqlCommandBuilder(_da);

      dataGridView1.DataSource = _dataTable;
      dataGridView1.DataMember = _dataTable.TableName;
      dataGridView1.AutoResizeColumns();

      _conn.Close();
  }   
  catch (Exception ex)
  {
     MessageBox.Show(ex.Message);
  }
  finally
  {
     if (_conn != null) _conn.Close();
  }
}

Why is this happening and how can I fix this? I've seen lot of questions about this but not a concrete answer for it.

score 1 · Accepted Answer · edited May 23 '17 at 12:12

You shouldn't build SQL statements like that.. use parameters instead. See SQL Injection. e.g,

var command = new SqlCommand("SELECT name,lastname,(@Row := @Row + 1) AS" 
                             " RowNumber FROM person JOIN (SELECT @Row := 0)"
                             " r WHERE name = @Name");
SqlParameter param  = new SqlParameter();
param.ParameterName = "@Name";
param.Value         = "John Doe";
command.Parameters.Add(param);

At any rate, that's not a connection string!

To use user-defined parameters, you need to set an option. The error you are seeing is a problem with your query because C# thinks you are trying to use @Row as a parameter. See How can I use a MySql User Defined Variable in a .NET MySqlCommand. Add this to your connection string ;Allow User Variables=True. There is also a blog post about this issue.

Also, what version of the MySQL data provider are you using? You may need to update.

I found this in the 5.0 documentation:

"Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL."

From this page: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlcommand.html

So using the @ symbol for the @Name parameter may not work in MySQL and you would need to use ?Name for parameters and @Row for user variables.

e.g,

var command = new SqlCommand("SELECT name,lastname,(@Row := @Row + 1) AS" 
                             " RowNumber FROM person JOIN (SELECT @Row := 0)"
                             " r WHERE name = ?Name");

Thank you on that note. Can you now show me how to apply that rule on my example from above. Note that my parameter is not "@firstname" and it does not have to be but "@Row". — Sylca, Dec 13 '12 at 13:25
Your effort made me learn something new and more and I'm grateful to you for that. It worked! I've done everything that you've wrote. In future I'll definitely use parameters in my statements. You're the man. Thank you — Sylca, Dec 13 '12 at 14:26

score -1 · Answer 2 · answered Dec 13 '12 at 13:18

-1

You're probably going to need to declare @Row in the query.

string command = "DECLARE @Row INT; SELECT name,lastname,(@Row := @Row + 1) AS RowNumber FROM person JOIN (SELECT @Row := 0) r WHERE name = @Name;";

_cmd = new MySqlCommand(_conn);
_cmd.CommandText = command;
_cmd.Parameters.Add("@Name", name);

answered Dec 13 '12 at 13:18

Dustin Kingen

20,677
7
52
92

Nice point, I've tried this, but its not helping me. Also, "@name" does not have to be parameter but "@Row" as I'm counting them. Any idea on how to that? – Sylca Dec 13 '12 at 13:29

Presence of @whatever in SQL command string gives me a fatal error

2 Answers2