.NET Buzz Forum - Parameterized Queries

Articles |
News |
Weblogs |
Books |
Forums

Artima Forums | Articles | Weblogs | Java Answers | News

Sponsored Link •

.NET Buzz Forum
Parameterized Queries - MySQL

0 replies on 1 page.

Welcome Guest
Sign In

Back to Topic List

Reply to this Topic

Search Forum

Threaded View


Previous Topic		Next Topic

Flat View: This topic has 0 replies on 1 page

David Cumps

Posts: 319
Nickname: cumpsd
Registered: Feb, 2004

David Cumps is a Belgian Student learning .NET

Parameterized Queries - MySQL

Posted: Apr 4, 2004 4:59 PM

This post originated from an RSS feed registered with .NET Buzz by David Cumps.
Original Post: Parameterized Queries - MySQL Feed Title: David Cumps Feed URL: http://weblogs.asp.net/cumpsd/rss?containerid=12 Feed Description: A Student .Net Blog :p	Latest .NET Buzz Posts Latest .NET Buzz Posts by David Cumps Latest Posts From David Cumps

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

Read: Parameterized Queries - MySQL

Previous Topic

Next Topic


	Web Artima.com