In order to do SQL right in the .Net world, you just don’t concaternate a search-term with a static search-string, because this will open all gates to SQL-injection. So the following schould not be used:
MySqlConnection connection = new MySqlConnection(_connectionString); MySqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT * FROM Forum where name = '" + forumName + "'";
Instead you should use a parameterized query. Easy, you might say. Just add a placeholder to the SQL-statement and off you go.
MySqlConnection connection = new MySqlConnection(_connectionString); MySqlCommand command = connection.CreateCommand(); MySqlParameter forumNameParameter = new MySqlParameter("@forumName", forumName); command.Parameters.Add(forumNameParameter); command.CommandText = "SELECT * FROM Forum where name = @forumName";
Unfortunatly this doesn’t seem to work alright. At least I didn’t get any results, even though my search-term did exist.
Looking at the actual SQL that was being executed on the server something became obvious.
SELECT * FROM Forum where name = @forumName
That’s not the SQL I was expecting. Somehow the parameter was not being substitued by the actual value. But why?
Just a short test: the same code does work on a MS-SQL database!
Solution
@
is not a valid character for a placeholder in MySQL. Instead ?
should be used. The correct code should look like this:
MySqlConnection connection = new MySqlConnection(_connectionString); MySqlCommand command = connection.CreateCommand(); MySqlParameter forumNameParameter = new MySqlParameter("?forumName", forumName); command.Parameters.Add(forumNameParameter); command.CommandText = "SELECT * FROM Forum where name = ?forumName";
So this finally worked.