I'm aware that SQL injection is rather dangerous. Now in my C# code I compose parameterized queries with SqlCommand class:
我知道SQL注入是相当危险的。现在在我的C＃代码中，我使用SqlCommand类编写参数化查询：
SqlCommand command = ...;
command.CommandText = "SELECT * FROM Jobs WHERE JobId = @JobId;";
command.Parameters.Add("@JobId", SqlDbType.UniqueIdentifier ).Value = actualGuid;
command.ExecuteNonQuery();
Will this automatically make my code immune to SQL injection? Do I have to do something extra?
这会自动使我的代码免受SQL注入吗？我需要做些额外的事吗？
5 个解决方案
#1
24
I'd say for your particular, and probably canonical, example for parametrized queries, yes it is sufficient.
对于参数化查询，我会说你的特定的，可能是规范的例子，是的，这就足够了。
However, people sometimes write code like this
但是，人们有时会编写这样的代码
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE col = @col;", tableName);
cmd.Parameters.Add("@col", ...);
because there is simply no way to pass the tablename itself as a parameter and the desire to do exists sometimes - misguided or not. It seems it is then often overlooked, that tableName (unless maybe only read from a set of static/constant values that do not derive from any input) indeed allows for SQL injection.
因为根本没有办法将tablename本身作为参数传递，并且有时存在的愿望存在 - 误导与否。它似乎经常被忽略，tableName（除非可能只读取一组不从任何输入派生的静态/常量值）确实允许SQL注入。
#2
17
According to the Note on this MSDN Article, "Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL."
根据此MSDN文章的注释，“特殊输入字符仅对动态SQL构成威胁，而不是在使用参数化SQL时。”
So I believe you are safe against SQL Injection. There might be some logical risks when using Identifiers like Idendity Values in your URLs but this is another story.
所以我相信你对SQL注入是安全的。在URL中使用Idendity Values等标识符时可能存在一些逻辑风险，但这是另一个故事。
#3
8
SQL Injection is mostly dependent on execution of dynamic SQL. In other words, SQL statements constructed by the concatenation of SQL with user-entered values.
SQL注入主要依赖于动态SQL的执行。换句话说，SQL语句与用户输入值的串联构造的SQL语句。
To avoid SQL Injection completely,
为了完全避免SQL注入，
Protecting yourself against SQL injection attacks is not very difficult. Applications that are immune to SQL injection attacks validate and sanitize all user input, never use dynamic SQL, execute using an account with few privileges, hash or encrypt their secrets, and present error messages that reveal little if no useful information to the hacker. By following a multi-layered approach to prevention you can be assured that if one defense is circumvented, you will still be protected.
保护自己免受SQL注入攻击并不是很困难。不受SQL注入攻击影响的应用程序验证并清理所有用户输入，从不使用动态SQL，使用具有少量权限的帐户执行，散列或加密其秘密，并显示错误消息，向黑客显示很少的有用信息。通过采用多层次的预防方法，您可以放心，如果一个辩护被规避，您仍然会受到保护。
From MSDN
来自MSDN
#4
1
Using SqlCommand a very good practice and as long as you don't concatenate SQL strings anywhere (including inside any stored procedures you call -- i.e. avoid dynamic SQL), you will be immune from SQL injection attacks.
使用SqlCommand是一种非常好的做法，只要你不在任何地方连接SQL字符串（包括你调用的任何存储过程内部 - 即避免使用动态SQL），你就可以免受SQL注入攻击。
#5
-1
You are not immune to SQL injection if you use dynamic sql, even if you are passing it through parameters. Too bad SQL Server doesn't have a built in function to sanitize parameters
如果使用动态sql，即使通过参数传递它，也不会免于SQL注入。太糟糕的SQL Server没有内置函数来清理参数
#1
24
I'd say for your particular, and probably canonical, example for parametrized queries, yes it is sufficient.
对于参数化查询，我会说你的特定的，可能是规范的例子，是的，这就足够了。
However, people sometimes write code like this
但是，人们有时会编写这样的代码
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE col = @col;", tableName);
cmd.Parameters.Add("@col", ...);
because there is simply no way to pass the tablename itself as a parameter and the desire to do exists sometimes - misguided or not. It seems it is then often overlooked, that tableName (unless maybe only read from a set of static/constant values that do not derive from any input) indeed allows for SQL injection.
因为根本没有办法将tablename本身作为参数传递，并且有时存在的愿望存在 - 误导与否。它似乎经常被忽略，tableName（除非可能只读取一组不从任何输入派生的静态/常量值）确实允许SQL注入。
#2
17
According to the Note on this MSDN Article, "Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL."
根据此MSDN文章的注释，“特殊输入字符仅对动态SQL构成威胁，而不是在使用参数化SQL时。”
So I believe you are safe against SQL Injection. There might be some logical risks when using Identifiers like Idendity Values in your URLs but this is another story.
所以我相信你对SQL注入是安全的。在URL中使用Idendity Values等标识符时可能存在一些逻辑风险，但这是另一个故事。
#3
8
SQL Injection is mostly dependent on execution of dynamic SQL. In other words, SQL statements constructed by the concatenation of SQL with user-entered values.
SQL注入主要依赖于动态SQL的执行。换句话说，SQL语句与用户输入值的串联构造的SQL语句。
To avoid SQL Injection completely,
为了完全避免SQL注入，
Protecting yourself against SQL injection attacks is not very difficult. Applications that are immune to SQL injection attacks validate and sanitize all user input, never use dynamic SQL, execute using an account with few privileges, hash or encrypt their secrets, and present error messages that reveal little if no useful information to the hacker. By following a multi-layered approach to prevention you can be assured that if one defense is circumvented, you will still be protected.
保护自己免受SQL注入攻击并不是很困难。不受SQL注入攻击影响的应用程序验证并清理所有用户输入，从不使用动态SQL，使用具有少量权限的帐户执行，散列或加密其秘密，并显示错误消息，向黑客显示很少的有用信息。通过采用多层次的预防方法，您可以放心，如果一个辩护被规避，您仍然会受到保护。
From MSDN
来自MSDN
#4
1
Using SqlCommand a very good practice and as long as you don't concatenate SQL strings anywhere (including inside any stored procedures you call -- i.e. avoid dynamic SQL), you will be immune from SQL injection attacks.
使用SqlCommand是一种非常好的做法，只要你不在任何地方连接SQL字符串（包括你调用的任何存储过程内部 - 即避免使用动态SQL），你就可以免受SQL注入攻击。
#5
-1
You are not immune to SQL injection if you use dynamic sql, even if you are passing it through parameters. Too bad SQL Server doesn't have a built in function to sanitize parameters
如果使用动态sql，即使通过参数传递它，也不会免于SQL注入。太糟糕的SQL Server没有内置函数来清理参数