VB.NET - Having some trouble with a parametized queries

I built a prototype system with some database queries. Since it was just a prototype and I was very new to databases, I just used a direct string. This is the string I used and it works fine:

command = New OleDbCommand("SELECT * FROM " + prefix + "CanonicForms WHERE Type=1 AND Canonic_Form='" + item + "'", dictionary_connection)

Now, in putting it in a real system, I wanted to use the more secure parametized method, so after some googling I came up with this:

command = New OleDbCommand("SELECT * FROM @prefix WHERE Type=1 AND Canonic_Form=@form", dictionary_connection)
command.Parameters.AddWithValue("@prefix", prefix + "CanonicForms")
command.Parameters.AddWithValue("@fo开发者_StackOverflowrm", item)

But all I get is an error for an incomplete query clause. What have I done differently between the two?

Your table name can't be a parameter. You might have to do some form of concatenation. It's not really a parameter in the formal sense of the word.

As ek_ny states, your table name can not be a parameter.

If you are really paranoid about injection then check the passed in table name against a white list of allowable values prior to buidling up the SQL string.

@cost, I agree that it would be nice but its just not a feature that exists. Generally its assumed that your WHERE clauses are the dynamic portion of the query and everything else, including the SELECT list and the table name, are static. In fact, most of the WHERE clause isn't even dynamic, only the search values themselves. You can't dynamically add column names this way either. This could definitely be built but it would require that the query engine be more aware of the database engine which is a can of worms that Microsoft didn't feel like opening.

Imagine a drop down menu with table names such as 'Jobs', 'People', 'Employers' that some naive developer built. On postback that value is used to SELECT * FROM. A simple SQL injection would be all that it takes to jump to another table not listed in that menu. And passing something really weird could very easily throw an error that could reveal something about the database. Value-only parametrized queries can still be broken but the surface area is much smaller.

Some people with multiple table prefixes use schemas, is that an option for you?