Node.js中如何让oracledb返回的JSON数值带引号

问题说明 #

使用Node.js的oracledb包从Oracle数据库获取发票明细数据，输出JSON时数值为数字类型（无引号），需要将所有数值转换为带引号的字符串类型。

当前输出：

{"postage_charge":0,"shipping_charge":52.28,"order_processing":9.82,"receiving_charge":0,"storage_charge":21.36,"product_charge":2.65,"sup_charge":0,"hold_charge":0}

期望输出：

{"postage_charge":"0","shipping_charge":"52.28","order_processing":"9.82","receiving_charge":"0","storage_charge":"21.36","product_charge":"2.65","sup_charge":"0","hold_charge":"0"}

解决方案 #

方案一：逐个字段转换为字符串 #

直接在构建result对象时，将每个数值字段转换为字符串类型，可使用String()方法或模板字符串实现：

var result = {
    "postage_charge": String(postageCharge.rows[0].POSTAL_CHARGE ?? 0),
    "shipping_charge": String(shippingCharge.rows[0].SHIPPING_CHARGE ?? 0),
    "order_processing": String(orderProcessing.rows[0].ORDER_CHARGE ?? 0),
    "receiving_charge": String(receivingCharge.rows[0].RECEIVING_CHARGE ?? 0),
    "storage_charge": String(storageCharge.rows[0].STORAGE_CHARGE ?? 0),
    "product_charge": String(productCharge.rows[0].PRODUCT_CHARGE ?? 0),
    "sup_charge": String(supCharge.rows[0].SUP_CHARGE ?? 0),
    "hold_charge": String(holdCharge.rows[0].HOLD_CHARGE ?? 0)
};

方案二：统一遍历转换数值字段 #

如果需要转换的字段较多，可封装工具函数遍历对象属性，将数值类型统一转为字符串：

function convertNumbersToStrings(obj) {
    const converted = {};
    for (const key in obj) {
        if (typeof obj[key] === 'number') {
            converted[key] = String(obj[key]);
        } else {
            converted[key] = obj[key];
        }
    }
    return converted;
}

// 构建原结果后调用转换函数
var result = {
    "postage_charge": postageCharge.rows[0].POSTAL_CHARGE ?? 0,
    "shipping_charge": shippingCharge.rows[0].SHIPPING_CHARGE ?? 0,
    "order_processing": orderProcessing.rows[0].ORDER_CHARGE ?? 0,
    "receiving_charge": receivingCharge.rows[0].RECEIVING_CHARGE ?? 0,
    "storage_charge": storageCharge.rows[0].STORAGE_CHARGE ?? 0,
    "product_charge": productCharge.rows[0].PRODUCT_CHARGE ?? 0,
    "sup_charge": supCharge.rows[0].SUP_CHARGE ?? 0,
    "hold_charge": holdCharge.rows[0].HOLD_CHARGE ?? 0
};

var convertedResult = convertNumbersToStrings(result);
return res.send(convertedResult);

重要优化：避免SQL注入风险 #

当前代码使用字符串拼接生成SQL语句（INVOICE_ID='${invoice}'），存在严重SQL注入风险，建议改用绑定变量：
以postageCharge的查询为例，修改为：

postageCharge = await connection.execute(
    `select sum(item_total) as postal_charge from INVOICE_ITEMS where INVOICE_ID = :invoice and charge_type='SHIPPING' and charge_subtype='POSTAGE'`,
    [invoice] // 绑定变量，自动处理字符串转义
);

所有SQL查询都需替换为绑定变量写法，确保代码安全性。

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