I've got a fully functionable (secure) session to a SQL Server database (version 10.50.4000). This is stored in a public variable:
我有一个完全可用的(安全)会话到SQL Server数据库(版本10.50.4000)。这存储在一个公共变量中:
SqlConnection conn = new SqlConnection();
I only want to run SELECT queries. For anything else, the user account got no rights.
我只想运行SELECT查询。除此之外,用户帐户没有权利。
The queries are built with only one user entry, which is inserted into a simple Text Box.
查询仅使用一个用户条目构建,该条目插入到简单的文本框中。
Unfortunately I must not tell you the original command text. So I make it simple for you:
不幸的是,我不能告诉你原始的命令文本。所以我简单地告诉你:
function print_users(string filtervalue)
{
SqlCommand cmd = null;
cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE '%@fv%'", this.conn)
cmd.Parameters.Add("@fv", SqlDbType.NVarChar);
cmd.Parameters["@fv"].Value=filtervalue;
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
//Do something with the answer from the DB
}
}
But this does not do the trick. I also tried AddWithValue, but I got no luck.
但这并不能解决问题。我也试过AddWithValue,但我没有运气。
When creating a stop-point on the line, where @fv should be replaced, I can go through the code line-by-line. And I can see that the command, where @fv should be replaced, is processed with no error. But @fv is not replaced (or at least I cannot see the replacement in the debug console).
在线上创建停止点时,应该替换@fv,我可以逐行查看代码。我可以看到,应该替换@fv的命令在没有错误的情况下处理。但@fv没有被替换(或者至少我在调试控制台中看不到替换)。
What am I doing wrong?
我究竟做错了什么?
EDIT:
thank you for your replies. Leaving out the single quotes ( ' ) did the trick.
谢谢您的回复。省略单引号(')就可以了。
And I also learned that this is not a string replacement. Thank you.
而且我还了解到这不是字符串替代品。谢谢。
Just one word: The connection is not left open all the time. It's immediately closed, when it's not needed any more; and re-established when needed again - I just forgot to write that into my sample code.
只需一个字:连接不会一直打开。当它不再需要时立即关闭;并在需要时重新建立 - 我只是忘了将其写入我的示例代码中。
Again: Thank you for your help!
再次:谢谢你的帮助!
2 个解决方案
#1
2
You can't see it being replaced in your debug session; the replacement occurs in the SQL server code itself...
您无法在调试会话中看到它被替换;替换发生在SQL服务器代码本身......
The client (your code) send both the SQL-string and the value of the parameter as seperate things to the server. There the SQL Engine 'replaces' the parameter with its value while executing it.
客户端(您的代码)将SQL字符串和参数的值作为单独的内容发送到服务器。在执行它时,SQL Engine会将参数替换为其值。
You should also put the 'wildcards' inside your parametervalue, not inside the query.
您还应该将'通配符'放在参数值内,而不是在查询中。
cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE @fv ", this.conn)
cmd.Parameters.Add("@fv", SqlDbType.NVarChar);
cmd.Parameters["@fv"].Value= "%" + filtervalue + "%";
#2
1
The parameter is not working because it is inside a string literal. You want to build the string like this:
该参数无效,因为它位于字符串文字内。你想构建这样的字符串:
cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE '%' + @fv + '%'");
While we're at it, keeping a global connection like that is bad. It can cause strange side effects, especially in web apps. Instead, keep a global connection string, and then use that string to create a new connection on each request.
虽然我们在这方面,但保持这样的全球联系是不好的。它可能会导致奇怪的副作用,尤其是在Web应用程序中。相反,保留一个全局连接字符串,然后使用该字符串在每个请求上创建一个新连接。
Also, "replace" is the wrong word here. Sql parameters are never replaced, even when they work. That's the whole point. There is no string replacement into your query at any point, ever. It's more like you declared an @fv variable at the server level in a stored procedure, and assigned your data directly to that variable. In this way, there is no possibility for a vulnerability in parameter replacement code, because the data portion of your query remains separate throughout the execution process. In same way, don't think in terms of "sanitizing" a parameter for a query; instead, think in terms of quarantining the data.
此外,“替换”在这里是错误的词。即使它们工作,也永远不会替换Sql参数。这就是重点。在任何时候都没有字符串替换您的查询。它更像是在存储过程中在服务器级别声明了@fv变量,并将数据直接分配给该变量。通过这种方式,参数替换代码中不存在漏洞,因为查询的数据部分在整个执行过程中保持独立。同样,不要考虑“清理”查询的参数;相反,请考虑隔离数据。
#1
2
You can't see it being replaced in your debug session; the replacement occurs in the SQL server code itself...
您无法在调试会话中看到它被替换;替换发生在SQL服务器代码本身......
The client (your code) send both the SQL-string and the value of the parameter as seperate things to the server. There the SQL Engine 'replaces' the parameter with its value while executing it.
客户端(您的代码)将SQL字符串和参数的值作为单独的内容发送到服务器。在执行它时,SQL Engine会将参数替换为其值。
You should also put the 'wildcards' inside your parametervalue, not inside the query.
您还应该将'通配符'放在参数值内,而不是在查询中。
cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE @fv ", this.conn)
cmd.Parameters.Add("@fv", SqlDbType.NVarChar);
cmd.Parameters["@fv"].Value= "%" + filtervalue + "%";
#2
1
The parameter is not working because it is inside a string literal. You want to build the string like this:
该参数无效,因为它位于字符串文字内。你想构建这样的字符串:
cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE '%' + @fv + '%'");
While we're at it, keeping a global connection like that is bad. It can cause strange side effects, especially in web apps. Instead, keep a global connection string, and then use that string to create a new connection on each request.
虽然我们在这方面,但保持这样的全球联系是不好的。它可能会导致奇怪的副作用,尤其是在Web应用程序中。相反,保留一个全局连接字符串,然后使用该字符串在每个请求上创建一个新连接。
Also, "replace" is the wrong word here. Sql parameters are never replaced, even when they work. That's the whole point. There is no string replacement into your query at any point, ever. It's more like you declared an @fv variable at the server level in a stored procedure, and assigned your data directly to that variable. In this way, there is no possibility for a vulnerability in parameter replacement code, because the data portion of your query remains separate throughout the execution process. In same way, don't think in terms of "sanitizing" a parameter for a query; instead, think in terms of quarantining the data.
此外,“替换”在这里是错误的词。即使它们工作,也永远不会替换Sql参数。这就是重点。在任何时候都没有字符串替换您的查询。它更像是在存储过程中在服务器级别声明了@fv变量,并将数据直接分配给该变量。通过这种方式,参数替换代码中不存在漏洞,因为查询的数据部分在整个执行过程中保持独立。同样,不要考虑“清理”查询的参数;相反,请考虑隔离数据。