PHP使用odbc_exec插入Access数据时提示查询表达式语法错误

报错信息 #

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'CHECKTIME BETWEEN ts '2021-08-01'} AND ts '2021-08-30'}'., SQL state 37000 in SQLExecDirect in C:\laragon\www\mondelez-report-access\getLog.php on line 38
37000

错误原因 #

1. SQL时间戳语法书写错误：ODBC标准的时间转义格式为{ts '日期时间字符串'}，生成的SQL中缺少左大括号，同时右大括号位置错误，导致Access无法识别语法。
2. 传入的日期参数仅包含年月日部分，直接匹配会漏掉结束日期当天带时间的记录。
3. 直接拼接用户输入参数到SQL语句，存在SQL注入安全隐患。

修复方案 #

方案1：修正时间戳转义语法（适配ODBC标准） #

修改getLog.php中的SQL查询部分，补全语法同时追加默认时间：

$query = "INSERT INTO TransactionLog (USERID, CHECKTIME, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID)
    SELECT CHECKINOUT.USERID, CHECKINOUT.CHECKTIME, USERINFO.Name, USERINFO.lastname, USERINFO.CardNo, DEPARTMENTS.DEPTID, DEPARTMENTS.DEPTNAME, DEPARTMENTS.SUPDEPTID
    FROM
    (CHECKINOUT
    LEFT JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID)
    LEFT JOIN DEPARTMENTS ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID
    WHERE CHECKTIME BETWEEN {ts '" . $from_date . " 00:00:00'} AND {ts '" . $to_date . " 23:59:59'} ";

方案2：改用Access原生日期格式（兼容性更好） #

Access支持用#直接包裹日期，写法更简洁，适配性更强：

$query = "INSERT INTO TransactionLog (USERID, CHECKTIME, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID)
    SELECT CHECKINOUT.USERID, CHECKINOUT.CHECKTIME, USERINFO.Name, USERINFO.lastname, USERINFO.CardNo, DEPARTMENTS.DEPTID, DEPARTMENTS.DEPTNAME, DEPARTMENTS.SUPDEPTID
    FROM
    (CHECKINOUT
    LEFT JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID)
    LEFT JOIN DEPARTMENTS ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID
    WHERE CHECKTIME BETWEEN #".$from_date." 00:00:00# AND #".$to_date." 23:59:59# ";

安全优化建议 #

建议将拼接SQL的写法替换为参数化查询，避免SQL注入风险，示例代码如下：

// 预编译SQL语句
$stmt = odbc_prepare($conn, "INSERT INTO TransactionLog (USERID, CHECKTIME, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID)
    SELECT CHECKINOUT.USERID, CHECKINOUT.CHECKTIME, USERINFO.Name, USERINFO.lastname, USERINFO.CardNo, DEPARTMENTS.DEPTID, DEPARTMENTS.DEPTNAME, DEPARTMENTS.SUPDEPTID
    FROM
    (CHECKINOUT
    LEFT JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID)
    LEFT JOIN DEPARTMENTS ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID
    WHERE CHECKTIME BETWEEN ? AND ?");
// 绑定参数执行
$letsgo = odbc_execute($stmt, [
    $from_date . " 00:00:00",
    $to_date . " 23:59:59"
]);

内容的提问来源于stack exchange，提问作者Fajar Dwi Rianto