PHP无法向Oracle数据库插入数据问题求助

你提供的代码核心问题在于直接拼接SQL字符串，这会引发SQL注入风险，同时字符转义错误（如字段值含单引号）、日期格式不匹配等问题都可能导致插入失败，且缺少错误捕获机制无法定位具体问题。

问题排查与修复步骤 #

• 确认数据库用户拥有WA_TKP_TBL_PART表的INSERT权限
• 强制使用绑定变量替换SQL拼接，避免字符转义和注入问题
• 添加错误捕获逻辑，获取执行失败的具体原因
• 手动提交事务（Oracle默认不自动提交）

修复后的完整代码 #

// 准备带占位符的INSERT语句
$qSave = oci_parse($c1, "INSERT INTO WA_TKP_TBL_PART (FORM_NO, CUSTOMERID_FK, MAKER_PART_CODE, SUPPLIERID_FK, MAKERID_FK, DESCRIPTION,
                        MSDS_FILE, ROHS2_FILE, TDS_FILE, DATEADDED, ADDEDBY, ISSUE_DATE, EXPIRE_DATE, ISO_9001_EXPIRE_DATE, ISO_14001_EXPIRE_DATE, EVIDENCE_FILE, ISO_FILE) VALUES(
                            :new_id, :getCustomer, :getMakerPartCodeC, :getSupplier, :getMaker, :getDescriptionC,
                            :newname3, :newname4, :newname2, TO_DATE(:date_added, 'dd-MON-yyyy hh24:mi:ss'), :getUserID, TO_DATE(:getIssueDate, 'dd-MON-yyyy'), TO_DATE(:getExpireDate, 'dd-MON-yyyy'),TO_DATE(:iso9001Expired, 'dd-MON-yyyy'),TO_DATE(:iso14001Expired, 'dd-MON-yyyy'), :newname6, :newname7)");

// 绑定所有变量，自动处理转义和类型匹配
oci_bind_by_name($qSave, ':new_id', $new_id);
oci_bind_by_name($qSave, ':getCustomer', $getCustomer);
oci_bind_by_name($qSave, ':getMakerPartCodeC', $getMakerPartCodeC);
oci_bind_by_name($qSave, ':getSupplier', $getSupplier);
oci_bind_by_name($qSave, ':getMaker', $getMaker);
oci_bind_by_name($qSave, ':getDescriptionC', $getDescriptionC);
oci_bind_by_name($qSave, ':newname3', $newname3);
oci_bind_by_name($qSave, ':newname4', $newname4);
oci_bind_by_name($qSave, ':newname2', $newname2);
oci_bind_by_name($qSave, ':date_added', $date_added);
oci_bind_by_name($qSave, ':getUserID', $getUserID);
oci_bind_by_name($qSave, ':getIssueDate', $getIssueDate);
oci_bind_by_name($qSave, ':getExpireDate', $getExpireDate);
oci_bind_by_name($qSave, ':iso9001Expired', $iso9001Expired);
oci_bind_by_name($qSave, ':iso14001Expired', $iso14001Expired);
oci_bind_by_name($qSave, ':newname6', $newname6);
oci_bind_by_name($qSave, ':newname7', $newname7);

// 执行并捕获错误
$executeStatus = oci_execute($qSave);
if (!$executeStatus) {
    $errorInfo = oci_error($qSave);
    // 线上环境建议写入日志，不要直接输出错误
    echo "插入失败：" . $errorInfo['message'];
    exit;
}

// 提交事务，Oracle默认需手动提交才能持久化数据
oci_commit($c1);

// 后续查询逻辑同样改用绑定变量
$qChkLastIDN = oci_parse($c1, "SELECT * FROM WA_GA_TBL_ACTIVITY WHERE TO_CHAR(ACTIVITYDATE, 'YYYY-MON') = :date_codeFormat");
oci_bind_by_name($qChkLastIDN, ':date_codeFormat', $date_codeFormat);
oci_execute($qChkLastIDN);

if(oci_fetch($qChkLastIDN) > 0)
{
    $qGetLastIDN = oci_parse($c1, "SELECT * FROM(SELECT * FROM WA_GA_TBL_ACTIVITY ORDER BY ACTIVITYID DESC) WHERE ROWNUM <= 1 AND TO_CHAR(ACTIVITYDATE, 'YYYY-MON') = :date_codeFormat");
    oci_bind_by_name($qGetLastIDN, ':date_codeFormat', $date_codeFormat);
    oci_execute($qGetLastIDN);
    $dGetLastIDN = oci_fetch_array($qGetLastIDN);
                            
    $getLastIDN = $dGetLastIDN['ACTIVITYID'];
    $cutGetLastIDN = substr($getLastIDN, 8);
                            
    $id_numN = substr($getLastIDN, 8) + 1;
    $id_numN = str_pad($id_numN, 6, "0", STR_PAD_LEFT);
    $dateCode = date("Ym");
    $id_letterN = "AC".$dateCode;

    $new_idN = $id_letterN . $id_numN;
}

$qCustomer = oci_parse($c1, "SELECT * FROM WA_GA_TBL_CUSTOMER WHERE CUSTOMERID = :getCustomer");
oci_bind_by_name($qCustomer, ':getCustomer', $getCustomer);
oci_execute($qCustomer);
$dCustomer = oci_fetch_array($qCustomer);
$getCustomerName = $dCustomer['CUSTOMER_NAME'];
                        
$qSupplier = oci_parse($c1, "SELECT * FROM WA_GA_TBL_SUPPLIER WHERE SUPPLIERID = :getSupplier");
oci_bind_by_name($qSupplier, ':getSupplier', $getSupplier);
oci_execute($qSupplier);
$dSupplier = oci_fetch_array($qSupplier);
$getSupplierName = $dSupplier['SUPPLIER_NAME'];
                        
$qMaker = oci_parse($c1, "SELECT * FROM WA_GA_TBL_MAKER WHERE MAKERID = :getMaker");
oci_bind_by_name($qMaker, ':getMaker', $getMaker);
oci_execute($qMaker);
$dMaker = oci_fetch_array($qMaker);
$getMakerName = $dMaker['MAKER_NAME'];
                        
$getResult = "partAdded";

核心优化点说明 #

1. 绑定变量：用:占位符替代字符串拼接，通过oci_bind_by_name绑定参数，彻底解决单引号等特殊字符导致的SQL语法错误，同时避免SQL注入。
2. 错误捕获：执行oci_execute后立即检查结果，用oci_error()获取详细错误信息，快速定位字段类型不匹配、必填字段为空等问题。
3. 事务提交：Oracle默认不会自动提交事务，必须调用oci_commit($c1)才能将插入的数据持久化到数据库。
4. 日期兼容性：如果Oracle环境的MON格式为中文月份（如'一月'），需将日期格式改为'dd/mm/yyyy'等通用格式，或直接绑定PHP日期类型变量。

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