I am executing a stored procedure on a SQL server using a SqlCommand class from C#. Currently I just build an execution string that parses in the parameter values to the stored procedure then executes the string on the server. The problem is when I have quotes the string does not get passed properly
我正在使用C#中的SqlCommand类在SQL服务器上执行存储过程。目前我只构建一个执行字符串,将参数值解析为存储过程,然后在服务器上执行字符串。问题是当我有引号时,字符串没有正确传递
Is it possible to use SqlParameter objects to pass in the parameters without worrying about escaping out of quotes?
是否可以使用SqlParameter对象传递参数而不必担心转出引号?
3 个解决方案
#1
1
Yes, that is the preferred way of sending parameters.
是的,这是发送参数的首选方式。
Example:
using (SqlCommand cmd = new SqlCommand("SomeProcedure", connection) {
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = 42;
cmd.Parameters.Add("@Text", SqlDbType.Varchar, 50).Value = "A string value";
cmd.ExecuteNonQuery();
}
#2
1
Simply put, yes - and you should be using SqlParameter objects rather than string concatenation anyway, as a minimum to guard against SQL injection attacks.
简单地说,是的 - 你应该使用SqlParameter对象而不是字符串连接,至少要防止SQL注入攻击。
#3
0
When using SqlParameter you are able to specify the type and size of the parameters. Doing so ensures execution plan re-use (though that's not such an issue with StoredProcs) but also ensure that you do not need to worry about escaping and quoting - it's all done for you.
使用SqlParameter时,您可以指定参数的类型和大小。这样做可以确保重复使用执行计划(虽然这不是StoredProcs的问题),但也确保您不必担心转义和引用 - 这些都是为您完成的。
#1
1
Yes, that is the preferred way of sending parameters.
是的,这是发送参数的首选方式。
Example:
using (SqlCommand cmd = new SqlCommand("SomeProcedure", connection) {
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = 42;
cmd.Parameters.Add("@Text", SqlDbType.Varchar, 50).Value = "A string value";
cmd.ExecuteNonQuery();
}
#2
1
Simply put, yes - and you should be using SqlParameter objects rather than string concatenation anyway, as a minimum to guard against SQL injection attacks.
简单地说,是的 - 你应该使用SqlParameter对象而不是字符串连接,至少要防止SQL注入攻击。
#3
0
When using SqlParameter you are able to specify the type and size of the parameters. Doing so ensures execution plan re-use (though that's not such an issue with StoredProcs) but also ensure that you do not need to worry about escaping and quoting - it's all done for you.
使用SqlParameter时,您可以指定参数的类型和大小。这样做可以确保重复使用执行计划(虽然这不是StoredProcs的问题),但也确保您不必担心转义和引用 - 这些都是为您完成的。