PHP提交表单数据到MSSQL无写入及数据库脱敏上传问题求助

根因说明 #

• index.php 中数据库选择下拉框缺少name属性，单选按钮组name属性不统一，导致参数无法正常提交
• request.php 直接拼接SQL语句存在语法错误（时间戳未加引号）且未做参数校验、防注入处理，SQL执行失败但未正确抛出错误
• 末尾多余空表单标签存在冗余冲突

修复后的代码 #

index.php 修改部分 #

<!-- 数据库选择下拉框添加name属性 -->
<div class="elem-group">
  <label for="database-selection">Database:</label>
      <select name="DatabaseName" id="database-selection" required>
        <option value="">Select a Database</option>
          <?php 
          $sql = "SELECT DatabaseName, DatabaseServer FROM databases";
          $result = sqlsrv_query($conn, $sql);
          while ($row = sqlsrv_fetch_array($result)) {
          echo '<option value="'.$row['DatabaseName'].'|'.$row['DatabaseServer'].'">'.$row['DatabaseName'].' ('.$row['DatabaseServer'].')</option>';
          }   
          ?>
        </select>
  </div>
<!-- 单选按钮统一name属性 -->
<div class="elem-group">
  <label for="randomize-database">Randomize Database?</label>
  <input type="radio" id="Yes" name="randomize" value="Yes" checked>Yes</input>
  <input type="radio" id="No" name="randomize" value="No" disabled>No</input>
</div>
<!-- 删除末尾多余的 <form action="request.php" method="POST"> 空标签 -->

request.php 修改部分（使用预处理语句避免注入和语法错误） #

<?php  
$server="database server";
$connectionInfo = array( "Database"=>"database", "UID"=>"user", "PWD"=>"password");
$conn = sqlsrv_connect($server,$connectionInfo);
if( !$conn ) {
 echo "Connection could not be established.<br />";
 die( print_r( sqlsrv_errors(), true));
}

// 先做参数校验
$name =  $_POST['requestor'] ?? '';
$email = $_POST['requestor_email'] ?? '';
$dbInfo = $_POST['DatabaseName'] ?? '';
$randomize = $_POST['randomize'] ?? 'Yes';
$timestamp = date('Y-m-d H:i:s');

if(empty($name) || empty($email) || empty($dbInfo)){
    die("参数错误，请返回重新填写");
}
list($databaseName, $databaseServer) = explode('|', $dbInfo);

// 使用预处理语句插入
$sql = "INSERT INTO dbo.requests (requestor_name, requestor_email, database_name, database_server, randomize, request_time, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
$params = array($name, $email, $databaseName, $databaseServer, $randomize, $timestamp, 'pending');
$stmt = sqlsrv_query($conn, $sql, $params);

if($stmt){
    echo "<h3>申请提交成功，我们将处理后通过邮件通知您下载链接</h3>";
    // 触发后台处理任务
    exec("powershell -ExecutionPolicy Bypass -File C:\scripts\db_process.ps1 -RequestId ".sqlsrv_insert_id($conn)." > NUL &");
}else{
    echo "ERROR: 申请提交失败 ";
    print_r(sqlsrv_errors());
}

sqlsrv_close($conn);
?>

采用PowerShell后台脚本处理耗时任务，避免前端请求超时，步骤如下：

1. 备份原数据库：通过Backup-SqlDatabase命令备份选中的业务库到本地临时目录
2. 脱敏处理：将备份恢复到临时脱敏实例，运行预设的脱敏SQL脚本替换敏感字段（手机号、身份证、地址等替换为随机合规值），完成后重新导出脱敏后的数据库备份包
3. 上传Dropbox：调用Dropbox API上传脱敏后的备份包，获取下载链接后更新到requests表，同时给申请人发送邮件通知

后台处理脚本示例（db_process.ps1） #

param($RequestId)
# 1. 查询申请信息
$connStr = "Server=database server;Database=database;UID=user;PWD=password;"
$request = Invoke-Sqlcmd -ConnectionString $connStr -Query "SELECT * FROM dbo.requests WHERE id = $RequestId"

# 2. 备份原数据库
$backupPath = "C:\temp\$($request.database_name)_$RequestId.bak"
Backup-SqlDatabase -ServerInstance $request.database_server -Database $request.database_name -BackupFile $backupPath

# 3. 恢复到脱敏实例并运行脱敏脚本
Restore-SqlDatabase -ServerInstance "DESENSITIZE_SERVER" -Database "temp_$RequestId" -BackupFile $backupPath
Invoke-Sqlcmd -ServerInstance "DESENSITIZE_SERVER" -Database "temp_$RequestId" -InputFile "C:\scripts\desensitize.sql"
Backup-SqlDatabase -ServerInstance "DESENSITIZE_SERVER" -Database "temp_$RequestId" -BackupFile "C:\temp\$($request.database_name)_desensitized_$RequestId.bak"

# 4. 上传到Dropbox
$token = "你的Dropbox API令牌"
$filePath = "C:\temp\$($request.database_name)_desensitized_$RequestId.bak"
$targetPath = "/db_backups/$($request.database_name)_desensitized_$RequestId.bak"
$headers = @{
    "Authorization" = "Bearer $token"
    "Dropbox-API-Arg" = '{"path": "'$targetPath'","mode": "add","autorename": true,"mute": false}'
    "Content-Type" = "application/octet-stream"
}
$response = Invoke-RestMethod -Uri "https://content.dropboxapi.com/2/files/upload" -Method Post -InFile $filePath -Headers $headers
$downloadLink = Invoke-RestMethod -Uri "https://api.dropboxapi.com/2/sharing/create_shared_link_with_settings" -Method Post -Headers @{Authorization = "Bearer $token"} -Body (@{path=$response.id} | ConvertTo-Json)

# 5. 更新申请状态
Invoke-Sqlcmd -ConnectionString $connStr -Query "UPDATE dbo.requests SET status='completed', download_link='$($downloadLink.url)' WHERE id=$RequestId"

# 6. 清理临时文件和库
Remove-Item $backupPath, $filePath
Invoke-Sqlcmd -ServerInstance "DESENSITIZE_SERVER" -Query "DROP DATABASE temp_$RequestId"

内容的提问来源于stack exchange，提问作者Aasim Shakil Pathan