Can I avoid all SQL-injection attacks by using parameters?
And don't worry about any thing in SQL injection in this case?
Or are there some types of these attacks which require more care on the part of the programmer?
我可以通过使用参数来避免所有SQL注入攻击吗?在这种情况下,不要担心SQL注入中的任何内容?或者是否有某些类型的攻击需要程序员更多的关注?
6 个解决方案
#1
10
No, you can't avoid all SQL injection attacks by using parameters. Dynamic SQL is the real issue, and this can occur in stored procedures as well as in your application code.
不,您无法通过使用参数来避免所有SQL注入攻击。动态SQL是真正的问题,这可能发生在存储过程以及应用程序代码中。
E.g., this is prone to a SQL injection attack: your parameterized query passes a username to a stored procedure, and within the stored procedure the parameter is concatenated to a SQL command and then executed.
例如,这容易发生SQL注入攻击:您的参数化查询将用户名传递给存储过程,并且在存储过程中,该参数被连接到SQL命令然后执行。
For an example of many kinds of SQL injection attacks, see this SQL Injection Cheat Sheet. You will see that simply escaping single quotes is just scratching the surface, and that there are many ways around that.
有关多种SQL注入攻击的示例,请参阅此SQL注入备忘单。您将看到,简单地转义单引号只是表面上的问题,并且有很多方法可以解决这个问题。
#2
9
Yes and no. Yes, if all of your SQL statements are indeed static and use only parameters, then you're 100% protected from SQL injection attacks.
是的,不是。是的,如果您的所有SQL语句都是静态的并且仅使用参数,那么您将100%受到SQL注入攻击的保护。
The problem comes when the parameters themselves are used to construct dynamic SQL statements. An example would be a stored procedure that generates a SQL statement dynamically for querying a multitude of different options, where a single monolithic statement would be impractical. While there are better solutions to this problem, this is a common one.
当参数本身用于构造动态SQL语句时,问题就出现了。一个示例是一个存储过程,它动态生成一个SQL语句,用于查询大量不同的选项,其中单个整体语句是不切实际的。虽然这个问题有更好的解决方案,但这是一个常见的解决方案。
#3
5
Yes you can avoid all SQL-injection attacks by using parameters, as long as you use parameters exclusively all the way down the call stack. For example:
是的,只要在调用堆栈中一直使用参数,就可以通过使用参数来避免所有SQL注入攻击。例如:
- Your app code calls a stored procedure or dynamic SQL in the database. That must use parameters to pass all values.
- The stored procedure or dynamic SQL internally constructs a call to another stored procedure or dynamic SQL statement. That must also use parameters to pass all values.
- Repeat ad-infinitum until you run out of code.
您的应用程序代码调用数据库中的存储过程或动态SQL。必须使用参数传递所有值。
存储过程或动态SQL在内部构造对另一个存储过程或动态SQL语句的调用。这也必须使用参数来传递所有值。
重复ad-infinitum直到你的代码用完为止。
If you are programming in SQL Server, you can use sp_executesql to execute dynamic SQL, and it will let you define and pass parameterised values to the statement being executed.
如果您使用SQL Server进行编程,则可以使用sp_executesql执行动态SQL,它将允许您定义参数化值并将其传递给正在执行的语句。
#4
2
If you are going to build a dynamic sql query with those parameters (passed to a stored procedure, for example) then there's a chance of sql injection if precautions are not taken.
如果您要使用这些参数构建动态sql查询(例如,传递给存储过程),那么如果不采取预防措施,则有可能进行sql注入。
#5
1
You can always minimize the risk of SQL injection by using prepared statements, provided your database engine supports them.
如果您的数据库引擎支持,您可以始终使用预准备语句来最小化SQL注入的风险。
Anyway, prepared statements is probably the most secure way of blocking SQL injections.
无论如何,准备好的语句可能是阻止SQL注入的最安全的方法。
#6
1
The problem is building the SQL statement dynamically.
问题是动态构建SQL语句。
For example, you might want to order the result based on the column the user selected. In most databases, you can't use parameters here ("ORDER BY ?" doesn't work). So you have to "ORDER BY " + column. Now, if "column" is a String, then the user of your web-application could inject code there (which is not easy, but possible).
例如,您可能希望根据用户选择的列对结果进行排序。在大多数数据库中,您不能在此处使用参数(“ORDER BY?”不起作用)。所以你必须“ORDER BY”+列。现在,如果“column”是一个String,那么你的web应用程序的用户可以在那里注入代码(这不容易,但可能)。
#1
10
No, you can't avoid all SQL injection attacks by using parameters. Dynamic SQL is the real issue, and this can occur in stored procedures as well as in your application code.
不,您无法通过使用参数来避免所有SQL注入攻击。动态SQL是真正的问题,这可能发生在存储过程以及应用程序代码中。
E.g., this is prone to a SQL injection attack: your parameterized query passes a username to a stored procedure, and within the stored procedure the parameter is concatenated to a SQL command and then executed.
例如,这容易发生SQL注入攻击:您的参数化查询将用户名传递给存储过程,并且在存储过程中,该参数被连接到SQL命令然后执行。
For an example of many kinds of SQL injection attacks, see this SQL Injection Cheat Sheet. You will see that simply escaping single quotes is just scratching the surface, and that there are many ways around that.
有关多种SQL注入攻击的示例,请参阅此SQL注入备忘单。您将看到,简单地转义单引号只是表面上的问题,并且有很多方法可以解决这个问题。
#2
9
Yes and no. Yes, if all of your SQL statements are indeed static and use only parameters, then you're 100% protected from SQL injection attacks.
是的,不是。是的,如果您的所有SQL语句都是静态的并且仅使用参数,那么您将100%受到SQL注入攻击的保护。
The problem comes when the parameters themselves are used to construct dynamic SQL statements. An example would be a stored procedure that generates a SQL statement dynamically for querying a multitude of different options, where a single monolithic statement would be impractical. While there are better solutions to this problem, this is a common one.
当参数本身用于构造动态SQL语句时,问题就出现了。一个示例是一个存储过程,它动态生成一个SQL语句,用于查询大量不同的选项,其中单个整体语句是不切实际的。虽然这个问题有更好的解决方案,但这是一个常见的解决方案。
#3
5
Yes you can avoid all SQL-injection attacks by using parameters, as long as you use parameters exclusively all the way down the call stack. For example:
是的,只要在调用堆栈中一直使用参数,就可以通过使用参数来避免所有SQL注入攻击。例如:
- Your app code calls a stored procedure or dynamic SQL in the database. That must use parameters to pass all values.
- The stored procedure or dynamic SQL internally constructs a call to another stored procedure or dynamic SQL statement. That must also use parameters to pass all values.
- Repeat ad-infinitum until you run out of code.
您的应用程序代码调用数据库中的存储过程或动态SQL。必须使用参数传递所有值。
存储过程或动态SQL在内部构造对另一个存储过程或动态SQL语句的调用。这也必须使用参数来传递所有值。
重复ad-infinitum直到你的代码用完为止。
If you are programming in SQL Server, you can use sp_executesql to execute dynamic SQL, and it will let you define and pass parameterised values to the statement being executed.
如果您使用SQL Server进行编程,则可以使用sp_executesql执行动态SQL,它将允许您定义参数化值并将其传递给正在执行的语句。
#4
2
If you are going to build a dynamic sql query with those parameters (passed to a stored procedure, for example) then there's a chance of sql injection if precautions are not taken.
如果您要使用这些参数构建动态sql查询(例如,传递给存储过程),那么如果不采取预防措施,则有可能进行sql注入。
#5
1
You can always minimize the risk of SQL injection by using prepared statements, provided your database engine supports them.
如果您的数据库引擎支持,您可以始终使用预准备语句来最小化SQL注入的风险。
Anyway, prepared statements is probably the most secure way of blocking SQL injections.
无论如何,准备好的语句可能是阻止SQL注入的最安全的方法。
#6
1
The problem is building the SQL statement dynamically.
问题是动态构建SQL语句。
For example, you might want to order the result based on the column the user selected. In most databases, you can't use parameters here ("ORDER BY ?" doesn't work). So you have to "ORDER BY " + column. Now, if "column" is a String, then the user of your web-application could inject code there (which is not easy, but possible).
例如,您可能希望根据用户选择的列对结果进行排序。在大多数数据库中,您不能在此处使用参数(“ORDER BY?”不起作用)。所以你必须“ORDER BY”+列。现在,如果“column”是一个String,那么你的web应用程序的用户可以在那里注入代码(这不容易,但可能)。