Node.js中含自定义值的INSERT SELECT查询实现咨询

Hey there! Let's break down how to safely implement your INSERT SELECT query (plus handle multiple INSERT statements) in Node.js, while avoiding SQL injection risks and keeping your code clean.

Core Concept: Adding Custom Values to INSERT SELECT #

Your original query copies data from leave_taken_superior_map to leave_taken_superior_map_dis_approved. To add custom values (like fixed strings or dynamic variables) that aren't coming from the SELECT source, simply include them as parameter placeholders in the SELECT clause, matching the column order in your INSERT statement.

Step-by-Step Implementation #

We'll use mysql2 (the modern, promise-based MySQL driver for Node.js) for this example—it's easier to work with async/await than the old callback-based mysql library.

1. Install Dependencies #

First, install the mysql2 package:

npm install mysql2

2. Safe Query with Custom Values & Multiple Statements #

Here's a complete, safe implementation. We'll include your original INSERT SELECT plus the second INSERT you mentioned, using parameter binding to avoid SQL injection:

const mysql = require('mysql2/promise');

async function executeBulkInserts() {
  // Establish database connection
  const connection = await mysql.createConnection({
    host: 'your-db-host',
    user: 'your-db-user',
    password: 'your-db-password',
    database: 'your-db-name',
    // Enable this only if you need to run multiple SQL statements in one call
    multipleStatements: true
  });

  try {
    // Define your custom values and query parameters
    const customMessage = 'Disapproved via bulk action';
    const targetGroupId = 456; // Replace with your actual group ID
    const affnoLeaveValue1 = 'HR-2024-001';
    const affnoLeaveValue2 = 'Annual Leave';

    // Combined SQL with parameter placeholders
    const combinedSql = `
      -- First: Insert from leave_taken_superior_map with custom value
      INSERT INTO leave_taken_superior_map_dis_approved 
      (ltsm_id,ltsm_superior_id,ltsm_group_id,ltsm_all_approved,ltsm_overide_by_hr,ltsm_old_list,ltsm_leave_type,ltsm_message,ltsm_user,lstm_leave_reason)
      SELECT 
        ltsm_id,ltsm_superior_id,ltsm_group_id,ltsm_all_approved,ltsm_overide_by_hr,ltsm_old_list,ltsm_leave_type,
        ?, -- Custom value for ltsm_message
        ltsm_user,lstm_leave_reason 
      FROM leave_taken_superior_map 
      WHERE ltsm_group_id = ?;

      -- Second: Your additional INSERT into affno_leave_take
      INSERT INTO affno_leave_take (affno_col1, affno_col2)
      VALUES (?, ?);
    `;

    // Execute query with parameters (order must match placeholders!)
    const [results] = await connection.execute(combinedSql, [
      customMessage, 
      targetGroupId, 
      affnoLeaveValue1, 
      affnoLeaveValue2
    ]);

    // Log results
    console.log(`First insert: ${results[0].affectedRows} rows added`);
    console.log(`Second insert: ${results[1].affectedRows} rows added`);

  } catch (error) {
    console.error('Query execution failed:', error);
  } finally {
    // Always close the connection
    await connection.end();
  }
}

// Run the function
executeBulkInserts();

Key Notes to Remember #

• Avoid SQL Injection: Never concatenate variables directly into your SQL string. Use ? placeholders and pass values as an array to execute()—the driver will handle proper escaping.
• Multiple Statements: If you need to run multiple SQL queries in one call, enable multipleStatements: true in your connection config. Only do this if all input values are trusted (which they are when using parameter binding).
• Custom Value Placement: To add a custom value, insert a ? in the SELECT clause at the position matching the corresponding column in your INSERT column list.
• Promise-Based Code: Using mysql2/promise with async/await makes your code more readable and easier to debug compared to callbacks.

If You Prefer the Old mysql Library #

If you're stuck using the callback-based mysql library, here's a quick adaptation:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'your-db-host',
  user: 'your-db-user',
  password: 'your-db-password',
  database: 'your-db-name',
  multipleStatements: true
});

connection.connect(err => {
  if (err) throw err;
  
  const params = ['Custom message', 456, 'HR-2024-001', 'Annual Leave'];
  const sql = `
    INSERT INTO leave_taken_superior_map_dis_approved 
    (ltsm_id,ltsm_superior_id,ltsm_group_id,ltsm_all_approved,ltsm_overide_by_hr,ltsm_old_list,ltsm_leave_type,ltsm_message,ltsm_user,lstm_leave_reason)
    SELECT ltsm_id,ltsm_superior_id,ltsm_group_id,ltsm_all_approved,ltsm_overide_by_hr,ltsm_old_list,ltsm_leave_type,?,ltsm_user,lstm_leave_reason FROM leave_taken_superior_map WHERE ltsm_group_id = ?;
    INSERT INTO affno_leave_take (affno_col1, affno_col2) VALUES (?, ?);
  `;

  connection.query(sql, params, (err, results) => {
    if (err) throw err;
    console.log(`First insert: ${results[0].affectedRows} rows`);
    console.log(`Second insert: ${results[1].affectedRows} rows`);
    connection.end();
  });
});

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