Parameterized Queries - MySQL

  • Posted on 2004-04-05 in .
  • (estimated time, 2 min)

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):

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

And I changed it to:

[csharp] 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; [/csharp]

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!

[csharp] 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; [/csharp]

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 ;)

Search

Categories