OLEDB 查询 SQL Server 失败

Question

我有两个 SQL 查询：

A.

SELECT (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + 
     upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr 
FROM atable ;

和

B。

SELECT (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + 
      upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr 
 FROM atable  WHERE userCompareStr='GAPYLE1111' ;

我有以下代码：

    Dim sql As String
    Dim conn As OleDbConnection
    Dim cmd As OleDbDataAdapter
    Dim ds As DataSet
    Dim tbl As DataTable

    conn = " something here "
    cmd = New OleDbDataAdapter(sql, conn)
    ds = New DataSet
    cmd.Fill(ds)
    tbl = New DataTable
    tbl = ds.Tables(0)

据我所知，当 sql 设置为字符串 A 时它似乎可以工作，但当它设置为字符串 B 时则不行。

这让我怀疑有该条款有问题 WHERE userCompareStr='GAPYLE1111'

我可以不以这种方式使用别名 userCompareStr 吗？我找不到这种用法的任何示例，但是当别名用于表名时，我确实找到了类似的用法——而且我没有看到任何反对这种用法的东西。

Answer 1

你有三个选择。

1）重复您在where中的选择中所做的事情

SELECT (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + 
      upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr 
FROM atable  

WHERE  (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + 
      upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) ='GAPYLE1111' ;

2）使用公共表表达式

with CTE AS 
(SELECT (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + 
      upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr 
FROM atable  )
SELECT userCompareStr FROM CTE where userCompareStr = 'GAPYLE1111';

3）内联查询请参阅Maziar Taheri的答案

顺便说一句，我希望“GAPYLE1111”不是来自用户输入，否则您将自己暴露在SQL注入攻击。使用参数化查询代替

Answer 2

不可以，您不能在 WHERE 子句中使用别名列。

请参阅 在 where 中使用别名列ms-sql 2000 中的子句

（本文是关于 SQL 2000 的，但今天仍然适用）

Answer 3

您不能在 where 子句内使用在 select 子句中设置的别名。

试试这个：

SELECT * FROM
(
SELECT (upper(rtrim(ltrim(lastname))) + upper(rtrim(ltrim(firstname))) + upper(rtrim(ltrim(middlename))) + rtrim(ltrim(v)) ) AS userCompareStr FROM atable)
as nested
WHERE userCompareStr='GAPYLE1111' ;

Answer 4

我暂时离开这个问题，去做其他事情，然后又回到这个问题上。
我通过从使用 oledb 切换到“其他东西”解决了主要问题。
我不确定新的（对我来说）方法叫什么 - 除了“本机 sqlserver？”

要点：

1. 不能在 WHERE 子句中使用字段名称别名。 （根据 maziar 和 matt）

2. Conrad 的修复 #1 适用于 OLEDB，但我不喜欢这种方法，因为它很冗长（并且真正的命令比我在这里提供的按比例缩小的示例要复杂得多）并且那里是很多不同的调用。容易出错且难以阅读（但在紧急情况下可以工作）。

3. 为了获得WITH或嵌套选择工作，我必须从OLEDB切换到“本机sqlserver”（或任何名称）。 WITH（如康拉德建议的那样）是我的首选解决方案 - 更容易阅读。当我从 OLEDB 切换到本机时，嵌套选择（由 Maziar 建议）也可以工作。

4. 我需要切换到“参数化查询”以避免 Conrad 指出的 SQL 注入攻击。

无论如何，当我切换到该方法时，上述建议有效。
我没有使用，而是

Provider=SQLOLEDB

使用：

providerName="System.Data.SqlClient"

我现在不引用 oledb（例如 oledbadapter），而是引用 sqlDataAdapter。我忽略了 upper、ltrim 和 trim 函数（因为事实证明它们不是问题）并专注于 oledb 一直困扰的WITH。这就是我要做的工作：

    Dim conn As New SqlConnection("server=localhost;database=DB;Integrated Security=SSPI;")
    Dim sql As String
    Dim da As SqlDataAdapter

    Dim ds As DataSet = New DataSet()
    Dim tbl As DataTable = New DataTable

    conn = New SqlConnection()
    conn.ConnectionString =    ConfigurationManager.ConnectionStrings("DB").ConnectionString

    Sql = " WITH cte AS "
    sql = sql & "(lastname + firstname + middlename"
    Sql = Sql & "     + v) as userCompareStr FROM atable ) "
    sql = sql & "SELECT userCompareStr   FROM cte WHERE userCompareStr = '" & "GAPYLE1111" & "' ;"

    da = New SqlDataAdapter(sql, conn)
    da.Fill(ds)
    tbl = ds.Tables(0)
    TextBox2.Text = sql

    If tbl.Rows.Count < 1 Then
        TextBox1.Text = "no items"
    Else
        TextBox1.Text = tbl.Rows.Count & " items selected"
    End If


    conn.Dispose()

另外，在 web.config 中，我添加了：

我还没有添加 SQL 注入修复内容，但我正在研究它，并且现在我确信这是我需要做的事情。我在这里找到了一些相关信息：

http://msdn.microsoft.com/en-us/library/ff648339.aspx

和这里：

http://weblogs.asp.net/cibrax/archive/2006/09/28/Parameterized-Queries-_2800_Oracle_2C00_-SQLServer_2C00_-OleDb_2900_.aspx

但我需要一些时间来尝试它。感谢您的帮助和指点。