VTRC Knowledge Manual (V2)

ธีม

6.4 · Service patterns + raw SQL + SQL injection ​

บทนี้เจาะลึกรูปแบบการเขียน SQL ของ benefit-api ซึ่งเป็นจุดที่มี technical debt หนักที่สุดของ service นี้ — มี SQL injection หลายสิบจุดเพราะใช้ template literal โดยไม่ parameterize

รูปแบบ raw SQL ที่ใช้ ​

มี 3 รูปแบบที่อยู่ใน codebase:

1. entityManager.query(templateLiteral) — พบบ่อยที่สุด, อันตรายที่สุด ​

ts
const result = await this.vtrcEntityManager.query(
  `SELECT * FROM MasterApprovers WHERE empCode = ${query.approverEmpCode}`,
);

ถ้า query.approverEmpCode มาจาก user input → SQL injection

2. getRepository().find/findAndCount/update — TypeORM active record style ​

ts
const result = await this.paymentTransactionRepository.find({
  where: { docUUID: id },
});

ปลอดภัย — TypeORM แปลงเป็น parameterized query อัตโนมัติ

3. createQueryBuilder() — พบน้อย ​

ts
const qb = this.paymentTransactionRepository.createQueryBuilder('pt');
qb.where('pt.docUUID = :id', { id });

ปลอดภัย — ใช้ named parameter

การค้นหา parameterized raw SQL — ไม่พบ ​

ผมค้นหา pattern .query(sql, [params]) (parameter-array syntax) ทั่วทั้ง codebase — ไม่พบการใช้ parameterized raw SQL แม้แต่จุดเดียว ทุก raw query ใน benefit-api คือ:

  • static string (ปลอดภัย) หรือ
  • template-literal interpolation (อันตราย)

SQL injection register — กรณีที่ยืนยันแล้ว ​

ทุกกรณีด้านล่างใช้ ${variable} interpolation ใน .query(\...`)` โดยที่ variable มาจาก user input หรือข้อมูลที่ user ควบคุมได้

Critical (user input ตรงๆ, ไม่ quote) ​

#ไฟล์:บรรทัดVariableEndpointSeverity
1approveDisaster.service.ts:416${query.approverEmpCode} (numeric, unquoted)GET approveDisaster/listBenefitForApproverCritical
2dropdown.service.ts:47${type.empCode} (numeric, unquoted)GET dropdown/get_dropdown_by_type (Public!)Critical

Code snippet — approveDisaster.service.ts:414-417 ​

414:417:benefit-api/src/modules/approveDisaster/approvedisaster.service.ts
      else if (!query.welfareType && query.approverEmpCode && query.isApprover == 'true' && query.approverEmpCode !== '') {
        const approverData = await this.vtrcEntityManager.query(
          `SELECT * FROM MasterApprovers ma WHERE empCode = ${query.approverEmpCode} AND isActive = 1`,
        );

query.approverEmpCode ไม่ถูกครอบด้วย quote และ interpolates ตรงเข้าไปใน SQL → classic 1 OR 1=1 vector

Code snippet — dropdown.service.ts:45-48 (Public endpoint!) ​

45:48:benefit-api/src/modules/dropdown/dropdown.service.ts
      if (type.isApprover == 'true' && type.empCode && type.empCode !== '') {
        const approverData = await this.vtrcEntityManager.query(
          `SELECT * FROM MasterApprovers ma WHERE empCode = ${type.empCode} AND isActive = 1`,
        );

endpoint นี้ @Public() — ไม่ต้อง auth ก็ยิงได้

High (user input, quoted แต่ interpolate) ​

#ไฟล์:บรรทัดVariableEndpoint
3dropdown.service.ts:100${query.empCode} (quoted)GET dropdown/getWelfareTypeListByEmp (Public)
4dropdown.service.ts:114${query.welfareTypeId}, ${query.orgCode}POST dropdown/getCOAList (Public)
5payments.service.ts:494${payment.docUUID} (data-derived)POST payments/cancelWelfare
6payments.service.ts:570-571${currentDate}, ${newOrderNo}, ${payment.docUUID} (UPDATE)POST payments/cancelWelfare
7payments.service.ts:1050${empIdentityCardString} (built from user input)POST payments/checkEmpDetail
8payments.service.ts:1054${profile.EmpID}, ${profile.SourceDB}(same)
9payments.service.ts:1141${wdHospitalData.hospitalBankAccountId}POST payments/updateEmpDetail
10payments.service.ts:1421-1422${newWDHospitalUpdate[...].orderNo/noteApprove/wdHospitalId} (UPDATE)POST payments/updateFromUpdateWDHospital
11payments.service.ts:1446${withdrawNo}findLastOrder helper
12export-payments.service.ts:512${payment.docUUID}POST exportPayments/add
13export-payments.service.ts:591-592${wdHospitalStatus}, ${currentDate}, ${newOrderNo}, ${payDateKeep}, ${payment.docUUID} (UPDATE)POST exportPayments/add
14export-payments.service.ts:1072${payment.docUUID}POST exportPayments/importBankTransactionFile
15exportpaymentreport.service.ts:6099${query.wdExportPaymentTransactionId} (numeric, unquoted)GET exportPaymentReport/getFileFmis
16exportpaymentreport.service.ts:6136,6140${query.wdExportPaymentTransactionId}(same chain)
17exportpaymentreport.service.ts:6170,6198${ft.id}, ${jsonDataSuccess.FILE_NAME...} (string built from SFTP-fetched JSON)SFTP-driven UPDATE
18exportpaymentreport.service.ts:7343,7361${query.wdExportPaymentTransactionId}getCheckFileStatusBgDoc/Ledger
19exportpaymentreport.service.ts:7398-7400${query.status}, ${startDate}, ${endDate}getExcelSummaryWithdraw
20exportpaymentreport.service.ts:7417-7442${startDate}, ${endDate} (4 queries)(same)

Code snippet — payments.service.ts:568-571 (UPDATE) ​

568:571:benefit-api/src/modules/payments/payments.service.ts
                    await vtrcTran.query(
                      `UPDATE WDHospitals
                       SET  status='CANCELDOC', updatedAt='${currentDate}', orderNo=${newOrderNo}
                       WHERE wdHospitalId='${payment.docUUID}'  and isHistory = 0
                      `
                    );

Code snippet — exportpaymentreport.service.ts:7398-7400 ​

7398:7400:benefit-api/src/modules/exportPaymentReport/exportpaymentreport.service.ts
        WHERE h.status = '${query.status}'
          AND h.createdAt >= '${startDate}'
          AND h.createdAt <= '${endDate}'

Lower risk (internally-derived date, แต่ pattern เดียวกัน) ​

Cron tasks interpolate bizDate เข้าไปใน HRMI queries — ค่า bizDate มาจาก WelfareUtil.getCurrentDate() ซึ่งสร้างจาก new Date().toISOString().slice(0,10) (welfare.util.ts:47-49) — ภายในควบคุมเอง ไม่ใช่ user-injectable แต่ pattern ยังเปราะบางและเสี่ยงถ้ามีการ refactor ในอนาคต

ไฟล์:บรรทัดรายละเอียด
inheritance.task.service.ts:261FORMAT(hd.ModifiedDate, 'yyyy-MM-dd') = '${bizDate}'
inheritance.task.service.ts:283pattern เดียวกัน
funeral.task.service.ts:512pattern เดียวกัน
funeral.task.service.ts:534pattern เดียวกัน

Static raw SQL (ปลอดภัย — ไม่มี interpolation) ​

ตัวอย่าง raw query ที่ยอมรับได้ (ไม่มี variable):

223:223:benefit-api/src/modules/payments/payments.service.ts
  const result = await this.paymentTransactionRepository.query('SELECT * FROM masterWelfareType');
6270:6273:benefit-api/src/modules/exportPaymentReport/exportpaymentreport.service.ts
        SELECT * FROM masterFmis where masterType = 'ACCOUNT_CODE'

(string literal 'ACCOUNT_CODE' — ปลอดภัย)

ผลกระทบเชิงโครงสร้าง ​

เนื่องจาก benefit-api ล็อกอินด้วย sa ในทุก connection (ดู 6.8 S11) SQL injection แม้แค่จุดเดียวก็เท่ากับ:

  • เข้าถึงข้อมูลทั้งหมดใน benefit, vtrc, dbHRMI_Center schema
  • execute xp_cmdshell หรือ MSSQL features อื่นเพื่อ RCE
  • อ่าน PII ของพนักงานทั้งหมด (จาก vtrc schema)

ดู 6.8 สำหรับ remediation priority

รูปแบบที่ควรเป็น (best practice) ​

TypeORM รองรับ parameterized query สองรูปแบบ:

รูปแบบที่ 1 — positional parameters:

ts
await this.vtrcEntityManager.query(
  'SELECT * FROM MasterApprovers WHERE empCode = ? AND isActive = 1',
  [empCode],
);

รูปแบบที่ 2 — QueryBuilder:

ts
await this.vtrcEntityManager
  .createQueryBuilder()
  .select('*')
  .from('MasterApprovers', 'ma')
  .where('ma.empCode = :empCode', { empCode })
  .andWhere('ma.isActive = 1')
  .getRawMany();

ทั้งสองรูปแบบไม่เคยปรากฏใน codebase ปัจจุบัน

ไปต่อ ​