Parameterized Queries with MySQL

Today I was looking over a project I’m currently working on, more specifically, at the SQL queries in it.

I come from a PHP background, where there is no such thing as parameterized queries. You simply build your own SQL string and make sure it doesn’t contain anything harmful.

Not having heard of such a thing as parameterized queries, I created my SQL statements the same way in C#, until I read about this practice being “not done”. So, I wanted to fix it.

I’m using MySQL with the MyODBC driver. But MySQL is tricky, it doesn’t support named parameters, so you have to use a question mark and add parameters in the right order.

No problem I thought, this would be a one-minute fix.

This is what I had (I returned an SQL query string at first):

return String.Format("INSERT INTO zosa_Users(UserVNaam, UserNaam, UserKlasNr, UserKlas) VALUES('{0}', '{1}', {2}, {3});", strFName, strGeslacht, intKlas, klKlas.Id);

And I changed it to:

OdbcCommand insertCmd = new OdbcCommand("INSERT INTO zosa_Users(UserVNaam, UserNaam, UserKlasNr, UserKlas) VALUES('?', '?', ?, ?);", zosaDb);
insertCmd.Parameters.Add(new OdbcParameter("", strFName));
insertCmd.Parameters.Add(new OdbcParameter("", strGeslacht));
insertCmd.Parameters.Add(new OdbcParameter("", intKlas));
insertCmd.Parameters.Add(new OdbcParameter("", klKlas.Id));
return insertCmd;

What did this insert in my database? Well it added a question mark ;)

So, I went looking for what was wrong… Did I add my parameters in a wrong way? Is there something wrong with MyODBC? After having done about everything I could think of, it was in the middle of the night and I went to bed. But today I tried something else: removing the single quotes ('?'). And it worked!

OdbcCommand insertCmd = new OdbcCommand("INSERT INTO zosa_Users(UserVNaam, UserNaam, UserKlasNr, UserKlas) VALUES(?, ?, ?, ?);", zosaDb);
insertCmd.Parameters.Add(new OdbcParameter("", strFName));
insertCmd.Parameters.Add(new OdbcParameter("", strGeslacht));
insertCmd.Parameters.Add(new OdbcParameter("", intKlas));
insertCmd.Parameters.Add(new OdbcParameter("", klKlas.Id));
return insertCmd;

Such a small thing, but I couldn’t find it anywhere; nobody ever posted a warning about it. Having no previous experience with parameters and the question mark, I simply thought it would safely replace the ? with my value, but still would require the quotes for string values.

Don’t make the same mistake! It’s a stupid one ;)