Parameterized Queries - MySQL

Today I was looking over a project I'm working on currently, 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.

So, not having heard of such 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): 
1return 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: 
1OdbcCommand insertCmd = new OdbcCommand("INSERT INTO zosa_Users(UserVNaam, UserNaam, UserKlasNr, UserKlas) VALUES('?', '?', ?, ?);", zosaDb); 

2insertCmd.Parameters.Add(new OdbcParameter("", strFName)); 

3insertCmd.Parameters.Add(new OdbcParameter("", strGeslacht)); 

4insertCmd.Parameters.Add(new OdbcParameter("", intKlas));

5insertCmd.Parameters.Add(new OdbcParameter("", klKlas.Id)); 

6return 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, remove the single quotes. And it worked! 
1OdbcCommand insertCmd = new OdbcCommand("INSERT INTO zosa_Users(UserVNaam, UserNaam, UserKlasNr, UserKlas) VALUES(?, ?, ?, ?);", zosaDb); 

2insertCmd.Parameters.Add(new OdbcParameter("", strFName)); 

3insertCmd.Parameters.Add(new OdbcParameter("", strGeslacht)); 

4insertCmd.Parameters.Add(new OdbcParameter("", intKlas));

5insertCmd.Parameters.Add(new OdbcParameter("", klKlas.Id)); 

6return insertCmd;
Such a small thing, but nowhere I managed to find this, nobody ever posted to watch out for this. Having no previous experiences 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 ;)

7 Comments

  • noted into memory :p



    small bug are hard to find :p

  • You should look into using the ByteFX MySQL library. It's a native MySQL interface for .NET, so you don't have to use ODBC or ODBC drivers. It also supports name parameters, which could certainly make the code easier to understand and update.



    Just a thought! I've been using the library for a project I've been working on, and it's worked great.

  • glad to know my asp.net thread was of use to someone else as well as me.



    drazic19

  • yes, it's really stupid mistake :)

  • I am having the same problems with interbase/firbird. I tried the ? marks and that did work. Does anyone know of a similar library for interbase/firebird so I can use named parameters? Thanks

  • Thanks Chris, I will have to give it a shot. Not sure I am going to be able to figure out how to get it to work, but I will try.

  • Thank you so much for posting this (and thank the lord for google for helping me find this post). I was banging my head against the wall with this same issue and being new to MySql I hadn't realized it didn't support paramertized queries. Thanks!

Comments have been disabled for this content.