2

I wrote a general method to search if a value exists in a specific table, the method is really simple:

public bool CheckRecordExist(int id, string tableName, string column)
{
    string connection = string.Format("Server=localhost; database={0}; UID=root; password=root; SslMode=none; CharSet=utf8;", "myDb");
    MySqlConnection conn = new MySqlConnection(connection);

    string query = "SELECT * FROM " + tableName + " WHERE " + column " = @" + column;
    MySqlCommand = new MySqlCommand(query, conn);
    MySqlCommand.Parameters.AddWithValue("@" + column, id);

   return Convert.ToBoolean(MySqlCommand.ExecuteScalar());
}

Suppose I want check if the record with id 2538782 exist on the table match, I do:

CheckRecordExist(2538782, "match", "id");

so the query variable will be:

SELECT * FROM match WHERE id = @id

if I run this in PhpMyAdmin, replacing the id with the value, I'll get the correct response, but inside my app I'll get:

MySql.Data.MySqlClient.MySqlException: '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 'match WHERE id = 2538782' at line 1'

what is wrong?

cojac
  • 85
  • 7
  • 1
    WARNING: SQL INJECTION RISK!!! – rory.ap May 02 '18 at 17:09
  • @rory.ap I think that is true of nearly all Stack Overflow SQL questions. – Bill Karwin May 02 '18 at 17:10
  • @rory.ap I run this locally on my machine, this app will never pushed online – cojac May 02 '18 at 17:10
  • @cojac, It doesn't matter if it's online, you can SQL injection yourself. Not all SQL injection problems are due to malicious web users. – Bill Karwin May 02 '18 at 17:11
  • @cojac -- That matters not. Why would you code it the sub-optimal way when it's so straightforward to use query params? – rory.ap May 02 '18 at 17:11
  • 1
    @rory.ap, It's not possible to use query parameters for table names or column names. – Bill Karwin May 02 '18 at 17:12
  • Also what you have posted wouldn't even compile, this is why we usually use `string.Format` or string interpolation to build short strings like this. – DavidG May 02 '18 at 17:13
  • I see a missing string concatenation: `WHERE " + column " = @" + column`. The `+` is missing after the first column reference. – Tommaso Belluzzo May 02 '18 at 17:13
  • @rory.ap I know, but please don't go off topic, my question is not about security – cojac May 02 '18 at 17:14
  • @cojac -- It's like if I said "WARNING: EXPLOSION RISK!!!" to a guy who was holding a lit match and a bottle of gasoline asking about why he can't seem to fit the match into the bottle. It doens't matter what the topic is: people could come across this question on this public site, and they should be made well aware that there is a major risk in the code you have provided. – rory.ap May 04 '18 at 18:58

1 Answers1

3

See https://dev.mysql.com/doc/refman/5.7/en/keywords.html

MATCH is a reserved keyword in MySQL.

You can use reserved words as identifiers only if you enclose them in delimiters. MySQL's default delimiter for identifiers is the back-tick.

See https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you Bill, this was the problem that afflicted me for hours, I'm grateful. Wish you a good day :) – cojac May 02 '18 at 17:17