Using variables in SQL queries in asp.net (C#)

Question

I have an SQL query of this form

string cmdText = "Select * from " + searchTable 
  + "WHERE " + searchTable 
  + "Name =' " +   searchValue + "'";

Basically what I am trying to do is get a particular actor's info from the database's Actors table. The variable searchTable has the value 'Actor' which is the table name and searchValue has the actor's name (which is represented by the ActorName attribute in the Actor's table, here I am trying to form the name of the attribute by concatenating the words 'Actor' and 'Name' )

So, well, all this concatenation results in (or at least should result in) a query of the form:

Select * from Actor where ActorName ='some actor';

But when I try to run this it gives me the error "Incorrect syntax near '=' " in the browser. Could anyone please help?

Answer 1

You can put (and should!) parameters into your SQL queries for the values in e.g. your WHERE clause - but you cannot parametrize stuff like your table name.

So I'd rewrite that query to be:

SELECT (list of columns)
FROM dbo.Actor
WHERE ActorName = @ActorName

and then pass in just the value for @ActorName.

If you need to do the same thing for directors, you'd have to have a second query

SELECT (list of columns)
FROM dbo.Directors
WHERE DirectorName = @DirectorName

Using parameters like this

• enhances security (prohibits SQL injection attacks!)
• enhances performance: the query plan for that query can be cached and reused for second, third runs

PS: the original problem in your setup is this: you don't have any space between the first occurence of your table name and the WHERE clause - thus you would get:

SELECT * FROM ActorWHERE ActorName ='.....'

If you really insist on concatenating together your SQL statement (I would NOT recommend it!), then you need to put a space between your table name and your WHERE !

Update: some resources for learning about parametrized queries in ADO.NET:

• The C# Station ADO.NET Tutorial / Lesson 06: Adding Parameters to Commands
• Using Parameterized Queries with the SqlDataSource

Answer 2

You shouldn't concatenate string to SQL, as this will open you up to SQL Injection attacks.

This is a rather long read about dynamic SQL, but worth reading to understand the risks and options.

You should be using parameterized queries instead, though the only way to use a table name as a parameter is to use dynamic SQL.

I urge you to change your approach regarding table names - this will lead to problems in the future - it is not maintainable and as I mentioned above, could open you to SQL Injection.

---

The error you are seeing is a result of the concatenation you are doing with the "Where " clause - you are missing a space before it. You are also adding a space after the ' in the parameter ending with "Name".

Your resulting string, using your example would be:

Select * from ActorWHERE ActorName =' some actor';

Answer 3

There is a blank missing and one too much:

searchTable + "Name =' "

should read

searchTable + " Name ='"

Beside that, use SQL parameters to prevent SQL injection.