mysql - 无法使用 INSERT 和检索 SQL 条目？

Question

问题是当我使用 ?用于将我的值与 JSON 名称对值一起传递的参数，mysql 行不会在该行中插入任何内容，但会插入空白值(使用 INSERT INTO 语句)。

以下是我 Node 中的一个函数:

function registerdone(req, res) {


    var username = req.body.username;
    var password = req.body.password;
    var firstname = req.body.firstname;
    var lastname = req.body.lastname;


    var encryptedPassword = bcrypt.hashSync(password, salt);
    console.log("encryptedPassword: " + encryptedPassword);
    var getUser = "INSERT INTO users (username, password, firstname, lastname) VALUES ('" + req.param("username") + "','" + encryptedPassword + "','" + req.param("firstname") + "','" + req.param("lastname") + "')";


    console.log("Query from registerdone is                       :" + getUser);

    mysql.fetchData(function(err, results) {
        if (err) {
            throw err;
            ejs.renderFile('./views/failRegister.ejs', function(err, result) {
                console.log('User with same Username already exists...');
            });
        } else {


            console.log(req.body.username + " Registered !!!");
            ejs.renderFile('./views/successRegister.ejs', function(err, result) {
                // render on success
                if (!err) {
                    res.end(result);
                }
                // render or error
                else {
                    res.end('An error occurred');
                    console.log(err);
                }
            });

        }
    }, getUser, queryParams);
}

这在我使用时非常有效:

var getUser = "INSERT INTO users (username, password, firstname, lastname) VALUES ('" + req.param("username") + "','" + encryptedPassword + "','" + req.param("firstname") + "','" + req.param("lastname") + "')";

但是当我使用时:

var getUser = "INSERT INTO users (username, password, firstname, lastname) VALUES ( ? ) ";
var queryParams = {
    'username': username,
    'password': encryptedPassword,
    'firstname': firstname,
    'lastname': lastname
    };

并使用回调函数发送 queryParams，我在 mysql 行中得到所有值为 null。

mysql DAO 是:

function getConnection(){
    var connection                 = mysql.createConnection({
        host                       : 'localhost',
        user                       : 'root',
        password                   : 'root',
        database                   : 'ebay_main',
        port                       : 3306
    });
    return connection;
}


function fetchData(callback, sqlQuery, queryParams){

    console.log("\nSQL Query       ::"+sqlQuery);

    var connection                 =getConnection();

    connection.query(sqlQuery, queryParams, function(err, rows, fields) {
        if(err){
            console.log("ERROR     : " + err.message);
        }
        else 
        {   // return err or result
            console.log("DB Results:"+JSON.stringify(rows));
            callback(err, rows);
        }
    });
    console.log("\nConnection closed..");
    connection.end();
}

Answer 1

您可以使用更简洁的语法:INSERT ??设置 ?

var table = 'users';
var queryParams = {
    'username': username,
    'password': encryptedPassword,
    'firstname': firstname,
    'lastname': lastname
};
connection.query('INSERT ?? SET ?', [table, queryParams ], function(err, rows, fields) {
    //...
});