Parameterized queries in MySQL

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.

Leave a Comment.