Hey there! I see you've got the foundation for your MySQL CRUD operations in place, but you're stuck on adding parameters to your functions. Let's fix that—plus we'll make sure your queries are safe from SQL injection by using parameterized queries (the mysql module's built-in ? placeholders).

First, let's recap why parameterized queries matter #

Instead of concatenating strings to build your SQL (which is risky for SQL injection), we'll use ? as placeholders for values. The mysql module will handle escaping these values safely for us.

Updated mysql.js with parameterized CRUD functions #

Here's the revised code with proper parameter support for each function:

const mysql = require('mysql')
const config = require('./dbconfig')

var pool = mysql.createPool({
  host: config.database.HOST,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE,
  port: config.database.PORT
});

let query = function(sql, values) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function(err, connection) {
      if (err) {
        reject(err) // Fixed: Reject instead of resolve on connection error
      } else {
        connection.query(sql, values, (err, rows) => {
          if (err) {
            reject(err)
          } else {
            resolve(rows)
          }
          connection.release()
        })
      }
    })
  })
}

// Get a user by ID
let findAUser = function(userId) {
  let _sql = `SELECT * FROM user WHERE id = ?`
  return query(_sql, [userId])
}

// Delete a user by ID
let deleteAUser = function(userId) {
  let _sql = `DELETE FROM user WHERE id = ?`
  return query(_sql, [userId])
}

// Update a user's name by ID
let updateAUser = function(newName, userId) {
  let _sql = `UPDATE user SET name = ? WHERE id = ?`
  return query(_sql, [newName, userId])
}

// Add a new user (adjust fields to match your table schema)
let addAUser = function(userData) {
  // Assuming your user table has `name` and `email` columns
  let _sql = `INSERT INTO user (name, email) VALUES (?, ?)`
  return query(_sql, [userData.name, userData.email])
}

module.exports = {
  findAUser,
  deleteAUser,
  updateAUser,
  addAUser
}

Key changes explained: #

• findAUser: Accepts a userId parameter, uses ? in the SQL, and passes the ID as an array to query.
• deleteAUser: Follows the same pattern as findAUser—targets the user to delete using the provided ID.
• updateAUser: Takes two parameters (newName and userId), with two ? placeholders in the SQL. The order of values in the array matches the order of placeholders.
• addAUser: Accepts a userData object (you can switch to individual parameters if you prefer), maps the object's properties to the SQL placeholders.
• Fixed the query function: It was resolving connection errors instead of rejecting them—now it properly rejects, so you can catch errors in your app code.

How to call these functions in app.js #

Here are examples of using each function in your Koa2 routes or middleware:

const userModel = require('./mysql');

// Example: Find a user with ID 1
userModel.findAUser(1)
  .then(rows => {
    console.log('Found user:', rows[0]); // Rows is an array of results
  })
  .catch(err => {
    console.error('Error finding user:', err);
  });

// Example: Delete user with ID 2
userModel.deleteAUser(2)
  .then(result => {
    console.log('Deleted rows count:', result.affectedRows);
  })
  .catch(err => {
    console.error('Error deleting user:', err);
  });

// Example: Update user ID 3's name to "Jane Doe"
userModel.updateAUser("Jane Doe", 3)
  .then(result => {
    console.log('Updated rows count:', result.affectedRows);
  })
  .catch(err => {
    console.error('Error updating user:', err);
  });

// Example: Add a new user
userModel.addAUser({ name: "John Doe", email: "john@example.com" })
  .then(result => {
    console.log('New user ID:', result.insertId);
  })
  .catch(err => {
    console.error('Error adding user:', err);
  });

Notes for customization: #

• Adjust the addAUser function to match your actual user table schema (add/remove columns as needed).
• If you prefer individual parameters over an object for addAUser, rewrite it like this:

  let addAUser = function(name, email) {
    let _sql = `INSERT INTO user (name, email) VALUES (?, ?)`
    return query(_sql, [name, email])
  }
  

  Then call it with userModel.addAUser("John Doe", "john@example.com").

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