prevent SQL注入动态的列名 [英] Prevent SQL Injection in Dynamic column names

问题描述

我无法自拔的不写一些动态sql情况在我的系统（使用的Postgres）的一部分

我的问题是如何最好地避免SQL注入与我目前使用的方法。

编辑（推理）：有很多列在一些表（一数目的增长（只）和其他地方维持）。我需要允许使用者决定哪个（predefined）柱他们要查询（如果必要，应用字符串功能）的方法。查询本身过于复杂，用户在写自己，也不必访问数据库。有1000的用户提供了不同的要求，我需要保持尽可能灵活 - 我不应该再讲code，除非主查询需要改变 - 而且，也没有办法知道什么条件的用户将需要使用

我有对象（通过Web服务接收），其产生的条件（产生方法如下 - 它是不是很完美），对于一些大型的SQL查询

。

在 _FieldName 是用户可编辑的（参数的名字，但它并不需要定），我很担心这可能是一个攻击向量。我把周围的双引号（看到带引号的标识符）在试图以消毒字符串字段名称，这样它可以永远不会成为一个关键字。我还可以查询的字段名称对字段列表，但它是很难得到及时维护。

不幸的是，用户必须输入条件的标准，我相信一定有更多的，我可以添加到sanatize方法？和它引用的列名，使其安全吗？ （我有限的测试，似乎也是这么认为的）。

为例内置的条件是和上（brandloaded.make）就像'O％'和上（brandloaded.make）不喜欢'OTHERBRAND'......

任何帮助或建议AP preciated。

 公共功能GetCondition（）作为字符串
   昏暗的SB作为新Text.StringBuilder

   把周围的报价表名，企图prevent一些SQL注入
   http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
   sb.AppendFormat（{0}，{1}，，_LogicOperator.ToString，_FieldName）

   选择案例_ConditionOperator
      案例ConditionOperatorOptions.Equals
          sb.Append（=）

      ...

   最终选择

   sb.AppendFormat（{0}，Me.UniqueParameterName）参数

   返回Me.Sanitize（SB）

端功能

专用功能消毒（BYVAL某人作为Text.StringBuilder）作为字符串

   '比较对这里提到了类似的黑名单：http://forums.asp.net/t/1254125.aspx

    sb.Replace（;，）
    sb.Replace（'，）
    sb.Replace（\，​​）
    sb.Replace（CHR（8），）

    返回sb.ToString

端功能

公共只读属性UniqueParameterName（）作为字符串
     得到
         返回String.Concat（：_UniqueIdentifier）
     最终获取
高端物业
 

解决方案

您可以从数据库中的列名和比较，以检查用户输入一个有效的列名。

I can't get away without writing some dynamic sql conditions in a part of my system (using Postgres).

My question is how best to avoid SQL Injection with the method I am currently using.

EDIT (Reasoning): There are many of columns in a number of tables (a number which grows (only) and is maintained elsewhere). I need a method of allowing the user to decide which (predefined) column they want to query (and if necessary apply string functions to). The query itself is far too complex for the user to write themselves, nor do they have access to the db. There are 1000's of users with varying requirements and I need to remain as flexible as possible - I shouldn't have to revisit the code unless the main query needs to change - Also, there is no way of knowing what conditions the user will need to use.

I have objects (received via web service) that generates a condition (the generation method is below - it isn't perfect yet) for some large sql queries.

The _FieldName is user editable (parameter name was, but it didn't need to be) and I am worried it could be an attack vector. I put double quotes (see quoted identifier) around the field name in an attempt to sanitize the string, this way it can never be a key word. I could also look up the field name against a list of fields, but it would be difficult to maintain on a timely basis.

Unfortunately the user must enter the condition criteria, I am sure there must be more I can add to the sanatize method? and does quoting the column name make it safe? (my limited testing seems to think so).

an example built condition would be "AND upper(brandloaded.make) like 'O%' and upper(brandloaded.make) not like 'OTHERBRAND'" ...

Any help or suggestions are appreciated.

Public Function GetCondition() As String
   Dim sb As New Text.StringBuilder

   'put quote around the table name in an attempt to prevent some sql injection
   'http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
   sb.AppendFormat(" {0} ""{1}"" ", _LogicOperator.ToString, _FieldName)

   Select Case _ConditionOperator
      Case ConditionOperatorOptions.Equals
          sb.Append(" = ")

      ...

   End Select

   sb.AppendFormat(" {0} ", Me.UniqueParameterName) 'for parameter

   Return Me.Sanitize(sb)

End Function

Private Function Sanitize(ByVal sb As Text.StringBuilder) As String

   'compare against a similar blacklist mentioned here: http://forums.asp.net/t/1254125.aspx

    sb.Replace(";", "")
    sb.Replace("'", "")
    sb.Replace("\", "")
    sb.Replace(Chr(8), "")

    Return sb.ToString

End Function

Public ReadOnly Property UniqueParameterName() As String
     Get
         Return String.Concat(":" _UniqueIdentifier)
     End Get
End Property

解决方案

You can get the column names from the database and compare to check the user has entered a valid column name.

这篇关于prevent SQL注入动态的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！