MySQL syntax error when attempting to POST data using express and postman-CodePudding

Im attempting to post some test data into a MYsql database with the following query:

Asset.create = (newAsset, result) => {
    sql.query("INSERT INTO `assets` VALUES (?, ?, ?, ?, ?)", newAsset, (err, res) => {
        if (err) {
            console.log("error", err);
            result(err, null);
            return;
        }

        console.log("Created new asset : ", { id: res.insertId, ...newAsset});
        result(null, {id: res.insertId, ...newAsset});
    });

using the following Json input in postman:

{
    "Id" : 6,
    "AccountID" : "6",
    "AssetID" : "6",
    "AssetSymbol" : "6",
    "Amount" : "60"

}

However I get the following error:

 sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?, ?)' at line 1",
  sql: "INSERT INTO `assets` VALUES (`id` = 6, `accountid` = '6', `assetid` = '6', `assetsymbol` = '6', `amount` = '60', ?, ?, ?, ?)"

The MySQL model for my assets table is as follows:

Id: INT (Not autoincrementing)
AccountID: VARCHAR(45)
AssetID: VARCHAR(45)
AssetSymbol: VARCHAR(45)
Amount: VARCHAR(45)

I know its a terrible design for a MySQL database. Its only a test database for now until I get the hang of using MySql with express and will make a new proper one later. Any advice on what Im doing wrong would be greatly appreciated.

CodePudding user response：

The normal node-mysql library doesn't support using an object like this. You should convert the object into an array:

Asset.create = (newAsset, result) => {
  sql.query("INSERT INTO `assets` VALUES (?, ?, ?, ?, ?)", Object.values(newAsset), (err, res) => {
    if (err) {
      console.log("error", err);
      result(err, null);
      return;
    }

    console.log("Created new asset : ", {
      id: res.insertId,
      ...newAsset
    });
    result(null, {
      id: res.insertId,
      ...newAsset
    });
  });
}

There are a number of packages that add named placeholders, you can find them with Google. Then you would be able to write:

Asset.create = (newAsset, result) => {
  sql.query("INSERT INTO `assets` VALUES (:Id, :AccountID, :AssetID, :AssetSymbol, :Amount)", newAsset, (err, res) => {
    if (err) {
      console.log("error", err);
      result(err, null);
      return;
    }

    console.log("Created new asset : ", {
      id: res.insertId,
      ...newAsset
    });
    result(null, {
      id: res.insertId,
      ...newAsset
    });
  });
}