为什么只有1行的DataTable.Load结果的行为就像是DataRow而不是DataTable? [英] Why does the result of DataTable.Load with just 1 row behave as if it is a DataRow instead of a DataTable?

问题描述

我想在不同服务器上的两个数据库中查询相同类型的信息，然后将结果合并到一个列表中.

我创建了这个示例，该示例可以运行，但前提是第一台服务器至少返回两行行.

该示例使用一个整数列表(1 .. 5)，但是将两个字符串列表连接起来的原始代码存在相同的问题.

 #'快速而肮脏的'示例#被认为是不安全的，#不要用作生产代码的基础.函数GetData($ dbserver，$ start，$ end){$ qry =使用q AS(SELECT $ start AS num UNION ALL SELECT num + 1 FROM q num< $ end)SELECT * FROM q"$ con =新对象System.Data.SqlClient.SqlConnection$ con.ConnectionString =服务器= $ dbserver;数据库= A2SDataCentral;集成安全性= SSPI"$ con.Open()$ com = $ con.CreateCommand()$ com.CommandText = $ qry$ res = $ com.ExecuteReader()$ table =新对象System.Data.DataTable$ table.Load($ res)返回$ table}$ all = $ null#清除前一次运行剩余的任何结果$ t1 = GetData(本地)" 1 2$ t2 = GetData(本地)" 3 5$ all = $ t1 + $ t2$全部 

我在这里使用的SQL查询只是一个生成序列号的虚拟对象.它将仅在SQL Server上工作.在此示例中，我在同一台服务器(本地主机)上运行了两次，实际上，它必须查询两个不同的服务器以获取两个名称列表并将它们加入.

如上使用，此示例做了我试图做的事情，最终结果是一个从1到5的整数列表.

数---1个2个345

但是当我将 GetData 调用更改为此时，使第一个调用仅返回一行，

  $ t1 = GetData(local)" 1 1$ t2 = GetData(本地)" 2 5 

它崩溃

方法调用失败，因为[System.Data.DataRow]不包含名为"op_Addition"的方法.

因此，如果结果中只有一行，那么看起来 GetData 函数不会返回 DataTable ，而是返回 DataRow .

我尝试了这个，但这也不是解决方案:

  $ all = $ t1$ all.Rows.Add($ t2) 

这将引发集合具有固定大小"( $ t1 具有2行)或您不能在空值表达式上调用方法"( $ t1 包含1行).

有人能解释为什么只有一行的结果会导致行为不同于多行吗?

(请注意脚本的示例/不安全性质)

解决方案

您是PowerShell的受害者，试图变得过于友好

当PowerShell 看到从管道上的函数传递的 DataTable 尝试对其进行分解并发送

或

 功能GetData{[CmdletBinding()]参数($ dbserver，$ start，$ end)$ qry =使用q AS(SELECT $ start AS num UNION ALL SELECT num + 1 FROM q num< $ end)SELECT * FROM q"$ con =新对象System.Data.SqlClient.SqlConnection$ con.ConnectionString =服务器= $ dbserver;数据库= A2SDataCentral;集成安全性= SSPI"$ con.Open()$ com = $ con.CreateCommand()$ com.CommandText = $ qry$ res = $ com.ExecuteReader()$ table =新对象System.Data.DataTable$ table.Load($ res)$ PSCmdlet.WriteObject($ table，$ false)} 

奖金安全审查！

我不能凭良心回答这个问题，除非指出您的函数当前容易受到最简单形式的SQL注入的攻击.<​​/p>

如果用户在哪里传递非数字值作为 $ start 或 $ end 参数自变量值怎么办?如果有人决定这样做:

  $ untrustedUserInput ='0 OR 1 = 1;xp_cmdshell"cmd/c calc.exe"-' 

恭喜，我现在拥有您的数据库服务器:)

在这种特定情况下，您可以轻松地减轻这种风险，但可以通过强烈键入您的参数:

 函数GetData([string] $ dbServer，[int] $ start，[int] $ end){... 

或

  param([string] $ dbServer，[int] $ start，[int] $ end) 

更好的解决方案是参数化您的SQL查询:

  $ qry =使用q AS(SELECT @start AS num UNION ALL SELECT num + 1 FROM q WHERE num< @end)SELECT * FROM q"#...$ com.Parameters.Add('@ start'，'Int').Value = $ start$ com.Parameters.Add('@ end'，'Int').Value = $ end 

这样，没有邪恶的字符串值就不会在不经过消毒的情况下潜入查询中

I want to query two databases on different servers for the same kind of information, and merge the results into a single list.

I created this example, which works, but only if the first server returns at least two rows.

The example uses a list of integers (1 .. 5), but my original code joining two lists of strings has the same problem.

# 'quick and dirty' sample
# to be considered insecure,
# do not use as a basis for production code.
function GetData($dbserver, $start, $end)
{
    $qry = "WITH q AS (SELECT  $start AS num UNION ALL SELECT num + 1 FROM q WHERE num < $end) SELECT * FROM q"
    $con = New-Object System.Data.SqlClient.SqlConnection
    $con.ConnectionString = "server=$dbserver;database=A2SDataCentral;Integrated Security=SSPI"
    $con.Open()
    $com = $con.CreateCommand()
    $com.CommandText = $qry
    $res = $com.ExecuteReader()
    $table = New-Object System.Data.DataTable
    $table.Load($res)
    return $table
}

$all = $null # erase any result remaining from a previous run
$t1 = GetData "(local)" 1 2
$t2 = GetData "(local)" 3 5
$all = $t1 + $t2
$all

The SQL query I use here is just a dummy that generates sequential numbers. It will work only on SQL server. In this example I run it at the same server (localhost) twice, in reality it has to query two different servers to get two lists of names and join them.

Used as above, this example does what I was trying to make it do, the end result is a list of integers from 1 through 5.

num
---
  1
  2
  3
  4
  5

But when I change the GetData calls into this, making the first call return only a single row,

$t1 = GetData "(local)" 1 1
$t2 = GetData "(local)" 2 5

it crashes with

Method invocation failed because [System.Data.DataRow] does not contain a method named 'op_Addition'.

So it looks like the GetData function returns not a DataTable, but a DataRow if there is only one row in the result.

I tried this, but that's not the solution either:

$all = $t1
$all.Rows.Add($t2)

This throws either "Collection was of a fixed size" ($t1 has 2 rows) or "You cannot call a method on a null-valued expression" ($t1 contains 1 row).

Can someone shine a light on why a result with only 1 row causes a different behavior from multiple rows?

(Edit: draw attention to example/insecure nature of script)

解决方案

You're a victim of PowerShell trying to be a little too friendly

When PowerShell sees a DataTable being passed from a function on the pipeline it attempts to unravel it and send the individual rows down-stream one by one.

This can be extremely useful when filtering a data set from a db query for example, but obviously extremely frustrating if you're relying on some intrinsic DataTable behavior.

To avoid having PowerShell unravel the rows, use Write-Output $table -NoEnumerator or - if you add a CmdletBinding attribute to your function - you can call $PSCmdlet.WriteObject($table,$false) directly:

function GetData($dbserver, $start, $end)
{
    $qry = "WITH q AS (SELECT  $start AS num UNION ALL SELECT num + 1 FROM q WHERE num < $end) SELECT * FROM q"
    $con = New-Object System.Data.SqlClient.SqlConnection
    $con.ConnectionString = "server=$dbserver;database=A2SDataCentral;Integrated Security=SSPI"
    $con.Open()
    $com = $con.CreateCommand()
    $com.CommandText = $qry
    $res = $com.ExecuteReader()
    $table = New-Object System.Data.DataTable
    $table.Load($res)

    Write-Output $table -NoEnumerate
}

or

function GetData
{
    [CmdletBinding()]
    param($dbserver, $start, $end)

    $qry = "WITH q AS (SELECT  $start AS num UNION ALL SELECT num + 1 FROM q WHERE num < $end) SELECT * FROM q"
    $con = New-Object System.Data.SqlClient.SqlConnection
    $con.ConnectionString = "server=$dbserver;database=A2SDataCentral;Integrated Security=SSPI"
    $con.Open()
    $com = $con.CreateCommand()
    $com.CommandText = $qry
    $res = $com.ExecuteReader()
    $table = New-Object System.Data.DataTable
    $table.Load($res)

    $PSCmdlet.WriteObject($table,$false)
}

Bonus security review!

I cannot in good conscience answer this question without pointing out that you're function is currently susceptible to the easiest form of SQL injection.

What if a user where to pass a non-numeric value as the $start or $end parameter argument value? What if someone decided to do:

$untrustedUserInput = '0 OR 1 = 1; xp_cmdshell "cmd /c calc.exe" --'

Congratulations, I now own your database server :)

In this specific instance you can easily mitigate this risk but strongly typing your parameters:

function GetData([string]$dbServer, [int]$start, [int]$end)
{
...

or

param([string]$dbServer, [int]$start, [int]$end)

The better solution is to parameterize your SQL query:

$qry = "WITH q AS (SELECT @start AS num UNION ALL SELECT num + 1 FROM q WHERE num < @end) SELECT * FROM q"
# ...
$com.Parameters.Add('@start','Int').Value = $start
$com.Parameters.Add('@end','Int').Value = $end

This way no evil string values will sneak into the query without being sanitized

这篇关于为什么只有1行的DataTable.Load结果的行为就像是DataRow而不是DataTable?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！