HTML/PHP表单无法向SQL Server提交数据问题求助

核心问题点 #

• 表单未设置enctype="multipart/form-data"，文件上传数据无法通过$_POST获取
• SQL Server对日期类型要求严格，直接传递HTML date输入值可能无法识别
• sqlsrv_query执行失败时不会抛出常规PHP异常，现有try-catch无法捕获数据库错误
• 未对用户输入做过滤处理，存在SQL注入风险，空值可能导致插入失败
• 文件上传逻辑错误：文件数据存储在$_FILES而非$_POST中

分步解决方案 #

1. 修复表单属性
   在form标签中添加enctype="multipart/form-data"，确保文件上传数据能被正确解析：

   <form class="post-form" action="process.php" method="post" enctype="multipart/form-data">
   

2. 处理日期参数
   用SQL Server的CONVERT函数将HTML日期字符串转为兼容的datetime类型：

   CONVERT(datetime, ?, 23)
   

   （23对应YYYY-MM-DD格式，和HTML date输入的输出格式匹配）

3. 添加错误检测
   替换无效的try-catch，改用sqlsrv_errors()捕获数据库操作错误：

   $result = sqlsrv_query($conn, $query, $parms);
   if ($result === false) {
       die(print_r(sqlsrv_errors(), true));
   }
   

4. 正确处理文件上传
   通过$_FILES获取文件信息，将文件保存到服务器后存储路径到数据库：

   $filePath = '';
   if (isset($_FILES['fileselect']) && $_FILES['fileselect']['error'] == UPLOAD_ERR_OK) {
       $uploadDir = 'uploads/';
       if (!file_exists($uploadDir)) mkdir($uploadDir, 0755, true);
       $filePath = $uploadDir . basename($_FILES['fileselect']['name']);
       move_uploaded_file($_FILES['fileselect']['tmp_name'], $filePath);
   }
   

5. 过滤输入与空值处理
   对用户输入做基本过滤，避免空值或恶意内容导致插入失败：

   $vendorname = isset($_POST['vendorname']) ? trim($_POST['vendorname']) : '';
   $location = isset($_POST['location']) ? trim($_POST['location']) : '';
   // 其他字段同理
   

修正后的完整代码示例 #

HTML代码 #

<form class="post-form" action="process.php" method="post" enctype="multipart/form-data">
    <label for="vendorname">Vendor Name</label>
    <input type="text" name="vendorname" id="vendorname" placeholder="Vendor Name" required>

    <label for="location">Location</label>
    <input type="text" name="location" id="location" placeholder="Location" required>

    <label for="price">Price</label>
    <input type="text" name="price" id="price" placeholder="Price" required>

    <label for="billingcycle">Billing Cycle</label>
    <select name="billingcycle" id="billing" required>
        <option value="monthly" selected>Monthly</option>
        <option value="anually">Anually</option>
    </select>

    <label for="startdate">Contract Start Date</label>
    <input type="date" name="startdate" id="startdate" required>

    <label for="enddate">Contract End Date</label>
    <input type="date" name="enddate" id="enddate" required>

    <label for="comments">Comments</label>
    <textarea name="comments" id="comments" cols="40" rows="8" placeholder="Comments"></textarea>
    
    <label for="fileselect">Attach File</label>
    <input id="fileselect" name="fileselect" type="file" accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel, image, .pdf" />

    <label for="filename">File Name</label>
    <input type="text" id="filename" name="filename"  >
    
    <input type="submit" class="submit" name="submit" >
</form>

PHP代码 #

<?php 
// 处理输入
$vendorname = isset($_POST['vendorname']) ? trim($_POST['vendorname']) : '';
$location = isset($_POST['location']) ? trim($_POST['location']) : '';
$price = isset($_POST['price']) ? trim($_POST['price']) : '';
$billingcycle = isset($_POST['billingcycle']) ? trim($_POST['billingcycle']) : '';
$startdate = isset($_POST['startdate']) ? $_POST['startdate'] : '';
$enddate = isset($_POST['enddate']) ? $_POST['enddate'] : '';
$comments = isset($_POST['comments']) ? trim($_POST['comments']) : '';
$filename = isset($_POST['filename']) ? trim($_POST['filename']) : '';

// 处理文件上传
$filePath = '';
if (isset($_FILES['fileselect']) && $_FILES['fileselect']['error'] == UPLOAD_ERR_OK) {
    $uploadDir = 'uploads/';
    if (!file_exists($uploadDir)) {
        mkdir($uploadDir, 0755, true);
    }
    $filePath = $uploadDir . basename($_FILES['fileselect']['name']);
    move_uploaded_file($_FILES['fileselect']['tmp_name'], $filePath);
}

// 数据库连接
$connectionInfo = array("Database"=>"your_db_name", "UID"=>"your_uid", "PWD"=>"your_pwd"); 
$conn = sqlsrv_connect("your_server_name", $connectionInfo);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}

// 插入数据
$query = "INSERT INTO dbName (vendorname, location, price, billingcycle, startdate, enddate, comments, fileselect, filename)
          VALUES (?, ?, ?, ?, CONVERT(datetime, ?, 23), CONVERT(datetime, ?, 23), ?, ?, ?)";
$parms = array($vendorname, $location, $price, $billingcycle, $startdate, $enddate, $comments, $filePath, $filename);
$result = sqlsrv_query($conn, $query, $parms);

if ($result === false) {
    die(print_r(sqlsrv_errors(), true));
} else {
    echo "Submission Successful";
}

// 关闭连接
sqlsrv_close($conn);
?>

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