Google Script读取Sheets日期变量执行MySQL查询报错咨询

• Google Apps Script字符串拼接变量语法错误：直接在SQL字符串内写startDate只会被识别为固定字符串，没有通过正确的拼接语法引入代码中定义的变量，导致直接语法报错
• 日期格式不兼容：你当前输出的MM/dd/yyyy格式不是MySQL date()函数的默认优先识别格式，即使拼接正确也可能出现日期解析错误
• 安全风险：直接拼接用户可修改的参数到SQL语句，存在SQL注入风险，生产环境禁用该写法

方案1：快速修复拼接语法（仅临时测试用） #

1. 先调整日期格式化规则，输出MySQL兼容的yyyy-MM-dd格式

// 替换原有日期格式化代码
var startDate = Utilities.formatDate(getStartDate,"GTM","yyyy-MM-dd");
var endDate = Utilities.formatDate(getEndDate,"GTM","yyyy-MM-dd");

2. 修改WHERE子句的字符串拼接逻辑，用+连接变量，变量外层补充SQL需要的单引号

'WHERE date(m.IncomingDate) >= date("' + startDate + '") and date(m.IncomingDate) <= date("' + endDate + '")\n' +  
'OR date(m.AppSet) >= date("' + startDate + '") and date(m.AppSet) <= date("' + endDate + '")\n' +

方案2：JDBC预处理语句（推荐生产环境使用） #

无需手动处理字符串拼接，完全规避语法错误和SQL注入风险，完整修改后的代码如下：

var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('DR_Campaign_Report');
var getStartDate = sheet.getRange(1,2).getValue();
var startDate = Utilities.formatDate(getStartDate,"GTM","yyyy-MM-dd");
var getEndDate = sheet.getRange(1,5).getValue();
var endDate = Utilities.formatDate(getEndDate,"GTM","yyyy-MM-dd");
var conn = Jdbc.getConnection(url, username, password);

// 定义SQL语句，使用?作为参数占位符
const querySql = `SELECT m.Campaign as "Campaign",
count(m.Campaign) as "Leads",
count(m.Duplicate) as "Dups",
count(m.Campaign) - count(m.Duplicate) as "Valid Leads",
count(m.AppSet) as "Appts",
SUM(IF(m.ZepID != "",1,0)) as "Transferred Appts"
FROM intakeMani m
WHERE date(m.IncomingDate) >= date(?) and date(m.IncomingDate) <= date(?)
OR date(m.AppSet) >= date(?) and date(m.AppSet) <= date(?)
GROUP BY m.Campaign with rollup`;

// 创建预处理语句对象
const preStmt = conn.prepareStatement(querySql);
// 按占位符顺序（从1开始计数）传入参数
preStmt.setString(1, startDate);
preStmt.setString(2, endDate);
preStmt.setString(3, startDate);
preStmt.setString(4, endDate);
// 执行查询
const results = preStmt.executeQuery();

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