为何环境变量会影响SQL查询执行？PowerShell技术问题

错误信息 #

PS C:\Path\To\Folder> .\main.ps1
Connected to the database: MY_INSTANCE on server: MY_DATABASE
"SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';"
Error: Exception calling "Fill" with "1" argument(s): "Could not find stored procedure 'SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';'."

已显式设置$command.CommandType = [System.Data.CommandType]::Text，但系统仍误将SQL文本识别为存储过程名。

相关文件内容 #

1. database.env配置文件 #

# database.env

# Configuration
DATABASE_DEFAULT_SERVER_INSTANCE="MY_INSTANCE"
DATABASE_DEFAULT_NAME="MY_DATABASE"
DATABASE_DEFAULT_USERNAME="MY_USER"
DATABASE_DEFAULT_PASSWORD="MY_PASSWORD"

# QUERIES
SELECT_INFORMATION_SCHEMA="SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';"

2. main.ps1核心代码 #

# main.ps1

. .\src\Functions\StartEnvVariables.ps1
Start-EnvVariables

Import-Module .\src\Modules\Database\DBFunctions.psd1
Import-Module .\src\Modules\StoredProcedures\CreateProcedures.psd1

$connection = Get-Connection
    
try {
    $connection.Open()
    
    Write-Host "Connected to the database: $($connection.Database) on server: $($connection.DataSource)"

    # ✔️ 正常运行
    # 硬编码查询
    #$query = "SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';"

    # ✖️ 运行失败
    # 读取环境变量
    $query = [System.Environment]::GetEnvironmentVariable("SELECT_INFORMATION_SCHEMA")

    Write-Host $query
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $command.CommandType = [System.Data.CommandType]::Text

    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataTable = New-Object System.Data.DataTable
    $adapter.Fill($dataTable)

    foreach ($row in $dataTable.Rows) {
        $schema = $row["table_schema"]
        $tableName = $row["table_name"]

        Start-CreateProcedures -SchemaName $schema -TableName $tableName
    }
}
catch {
    Write-Host "Error: $($_.Exception.Message)"
}
finally {
    $connection.Close()
}

3. 环境变量加载函数 #

function Start-EnvVariables {
    Get-Content .\config\database.env | ForEach-Object {
        $indexOfEquals = $_.IndexOf('=')
        if ($indexOfEquals -ge 0) {
            $name = $_.Substring(0, $indexOfEquals).Trim()
            $value = $_.Substring($indexOfEquals + 1).Trim()
            
            [System.Environment]::SetEnvironmentVariable($name, $value, [System.EnvironmentVariableTarget]::Process)
        }
    }
}

已尝试的操作 #

• 将环境变量内容强制转换为字符串：

  $query = ([System.Environment]::GetEnvironmentVariable("SELECT_INFORMATION_SCHEMA")).toString()
  

• 调整SQL语句中的单引号写法，问题未解决。

从Write-Host $query的输出可以看到，查询字符串被包裹在双引号中。这是因为加载.env文件时，没有去除值两端的引号，导致数据库执行时把包含双引号的整个内容当作存储过程名，从而报错。

修改Start-EnvVariables函数，在提取值后去除两端的双引号，同时增加注释和空行的跳过逻辑：

function Start-EnvVariables {
    Get-Content .\config\database.env | ForEach-Object {
        $line = $_.Trim()
        # 跳过注释行和空行
        if ($line -match '^#|^$') { return }
        
        $indexOfEquals = $line.IndexOf('=')
        if ($indexOfEquals -ge 0) {
            $name = $line.Substring(0, $indexOfEquals).Trim()
            $value = $line.Substring($indexOfEquals + 1).Trim()
            
            # 移除值两端的双引号
            if ($value.StartsWith('"') -and $value.EndsWith('"')) {
                $value = $value.Substring(1, $value.Length - 2)
            }
            
            [System.Environment]::SetEnvironmentVariable($name, $value, [System.EnvironmentVariableTarget]::Process)
        }
    }
}

• 可扩展逻辑支持去除单引号包裹的值，适配更多配置场景；
• 增加对异常格式行的容错处理，避免加载失败。

内容的提问来源于stack exchange，提问作者Ronald Jimenez