Node.js MySQL Connection and SQL Injection Prevention Methods

Install mysql

$ yarn add mysql

Create connection pool, common SQL statements

// mysql.js
const mysql = require('mysql');
// Create connection pool
const pool = mysql.createPool({
    host: 'xxx.xx.xxx.xxx', // Server IP
    port: 3306, // mySql running port (default 3306)
    database: 'name', // Database name
    user: 'user', // Database username
    password: xxxx // Database password
})
const query = (sql, values) => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) {
                reject(err)
            } else {
                connection.query(sql, values, (err, rows) => {
                    if (err) {
                        reject(err)
                    } else {
                        resolve(rows)
                    }
                    connection.release()
                })
            }
        })
    })
}

/**

Query data
@param {*} table Table name
@param {*} key Query condition key
@returns list
 */
const findDataWhere = (table, word, key = 'id') => {
 const _sql = SELECT * FROM ??  WHERE ${key} =?
 return query(_sql, [table, word])
}

/**

Insert data
@param {*} table Table name
@param {*} values Values to insert
@returns list
 */
const insertData = (table, values) => {
 const _sql = INSERT INTO ?? SET ?
 return query(_sql, [table, values])
}

/**

Update data
@param {*} table Table name
@param {*} values Values to insert
@param {*} word Value of the query condition
@param {*} key Query condition key
@returns list
 */
const updateData = (table, values, word, key = 'id') => {
 const _sql = UPDATE ?? SET ? WHERE ${key} =? 
 return query(_sql, [table, values, word])
}

/**

Delete data
@param {*} table Table name
@param {*} word Value of the query condition
@param {*} key Query condition key
@returns 
 */
const deleteData = (table, word, key = "id") => {
 const _sql = DELETE FROM ?? WHERE ${key} =?
 return query(_sql, [table, word])
}
module.exports = {
 query,
 insertData,
 updateData,
 deleteData,
 findDataWhere
}

How to use

        <h4 data-id="heading-3" style="font-size:14px;margin-top:30px;margin-bottom:10px;padding-left:24px;position:relative;color:#00325E;font-family:-apple-system, system-ui, &quot;white-space:normal;background-color:#FFFFFF;">
            <span style="font-weight:normal;">

// Controller layer article.js
const mySqlFun = require("./mysql")
/**
 * Get tag list (id descending) with pagination
 */
const findLabelListPage = async (ctx) => {
    let { pageIndex = 1, pageSize = 10, } = ctx.query
    let resData = {
        list: [],
        pageIndex: pageIndex,
        pageSize: pageSize,
    }
    const _sql = `SELECT * FROM ?? ORDER BY id DESC LIMIT ${(pageIndex - 1) * pageSize},${pageSize} `
    // Use the exported query function; first parameter is sql, second is array; ?? and ? are placeholders for the actual table name or parameters
    let res = await mySqlFun.query(_sql, ['label'])
    if (res) {
       ...
    } else {
       ...
    }
}