在asp.net中使用SQL查询中的变量（C＃）

I have an SQL query of this form

我有这个表单的SQL查询

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

基本上我要做的是从数据库的Actors表中获取特定actor的信息。变量searchTable的值为'Actor',即表名,searchValue具有actor的名称(由Actor表中的ActorName属性表示,这里我试图通过连接单词'Actor来形成属性的名称'和'姓名')

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?

但是当我尝试运行它时,它在浏览器中给出了“错误的语法'=''”。有人可以帮忙吗?

4 个解决方案

#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.

您可以将(并且应该!)参数放入SQL查询中以获取例如中的值。你的WHERE子句 - 但你不能参数化你的表名之类的东西。

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.

然后传入@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!)

增强安全性(禁止SQL注入攻击!)

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:

PS:你的设置中的原始问题是:你的表名第一次出现和WHERE子句之间没有任何空间 - 因此你会得到:

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 !

如果你真的坚持将你的SQL语句连接在一起(我不推荐它!),那么你需要在你的表名和你的WHERE之间加一个空格!

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

更新:在ADO.NET中学习参数化查询的一些资源:

The C# Station ADO.NET Tutorial / Lesson 06: Adding Parameters to Commands

C#Station ADO.NET教程/第06课:向命令添加参数

Using Parameterized Queries with the SqlDataSource

使用SqlDataSource的参数化查询

#2

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

您不应该将字符串连接到SQL,因为这会打开SQL注入攻击。

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

这是关于动态SQL的相当长的阅读,但值得阅读以了解风险和选项。

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

您应该使用参数化查询,但使用表名作为参数的唯一方法是使用动态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.

我敦促你改变你对表名的态度 - 这将导致将来出现问题 - 它是不可维护的,正如我上面提到的,可以打开你的SQL注入。

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".

您看到的错误是您使用“Where”子句进行连接的结果 - 您之前缺少一个空格。您还在“以名称”结尾的参数中添加了一个空格。

Your resulting string, using your example would be:

您的结果字符串,使用您的示例将是:

Select * from ActorWHERE ActorName =' some actor';

#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.

除此之外,使用SQL参数来防止SQL注入。

#4

-1

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

#1