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'm trying to do is get specific information about the actor from the database. The variable searchTable has the value "Actor", which is the name of the table, and searchValue has the name of the actor (which is represented by the ActorName attribute in the Actor table, here I am trying to form the attribute name by combining the words "Actor" 'and' Name ')

So, all this concatenation leads to (or at least should) a form request:

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

But when I try to run this, it gives me the error "Incorrect syntax next to = =" in the browser. Can anyone help?

Answer 1

You can put (and in !) Parameters in your SQL queries for values, for example. your WHERE , but you cannot parameterize the material, for example, the name of your table.

So, I would rewrite this query:

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

and then enter only the value for @ActorName .

If you need to do the same for directors, you will have to have a second request

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

Using options like

• increases security (prohibits SQL injection attacks!)
• improves performance: the query plan for this request can be cached and reused for the second, third run

PS: the initial problem in your setup is this: you have no place between the first input of your table name and the WHERE - this way you get:

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

If you really insist on joining your SQL statement (I would recommend NOT !), Then you need to put a space between the name of your table and your WHERE !

Update: some resources for learning parameterized queries in ADO.NET:

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

Answer 2

You should not concatenate a string with SQL, as this will open SQL Injection attacks for you.

It reads quite a while about dynamic SQL, but it is worth reading to understand the risks and options.

Instead, you should use parameterized queries , although the only way to use the table name as a parameter is to use dynamic SQL.

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

---

The error you see is the result of the concatenation you make with the Where clause - you are running out of space before that. You also add a space after ' in the parameter ending in "Name".

Your summary line, using your example, will look like this:

 Select * from ActorWHERE ActorName =' some actor'; 

Answer 3

Missing space and too much:

 searchTable + "Name =' " 

must read

 searchTable + " Name ='" 

Also, use SQL parameters to prevent SQL injection.

Answer 4

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