节点js - Mysql查询未按编码执行

Question

我在node.js中创建了几个sql语句，现在我想在我的db上执行它们。但是，查询字符串不是按编码方式执行的。

这是我用来生成查询字符串的函数。

function insertProducts(products) {
    if (!connection) {
        // Create MYSQL-Connection
        console.log('BUILDING connection to DB');
        connection = getConnection();
        connection.connect();
    }

    let query = "";
    for (let i = 0; i < products.length; i++) {
// Iterate trough the products array and create a sql query
        query += "INSERT INTO `tShortDescription`(`ShortDescription`, `Language`) VALUES ('" + products[i].short_description + "', 'DE'); " +
            "INSERT INTO `tDescription`(`Description`, `Language`) VALUES ('" + products[i].description + "', 'DE'); " +
            "INSERT INTO `tManufacturer`(`Name`) VALUES ('" + products[i].manufactur + "'); " +
            "INSERT INTO `tSupplier`(`Name`) VALUES ('" + products[i].supplier + "'); " +

            "INSERT INTO `tProduct`(`Sku`, `Title`, `ShortDescriptionId`, `DescriptionId`, `WohlesalePrice`, `SellingPrice`, `Quantity`, " +
            "`ManufacturerId`, `SupplierId`, `Ean`) VALUES ('" + products[i].sku + "', '" + products[i].name + "', " +
            "(SELECT id FROM tShortDescription WHERE ShortDescription = '" + products[i].short_description + "' LIMIT 1), " +
            "(SELECT id FROM tDescription WHERE Description LIKE '" + products[i].description + "' LIMIT 1), " +
            products[i].wholesale_price + ", " + products[i].selling_price + ", " + products[i].quantity + ", " +
            "(SELECT id FROM tManufacturer WHERE Name = '" + products[i].manufactur + "' LIMIT 1), " +
            "(SELECT id FROM tSupplier WHERE Name = '" + products[i].supplier + "' LIMIT 1), " + products[i].ean + "); ";

        for (let j = 0; j < products[i].categories.length; j++) {
            // Ad all categories to query
            query += "INSERT INTO `rtCategory`(`ProductId`, `CategoryId`) " +
                "VALUES ((SELECT `Id` FROM `tProduct` WHERE sku = '" + products[i].sku + "' LIMIT 1), " +
                "(SELECT `Id` FROM `tCategory` WHERE Id = " + products[i].categories[j].src + " LIMIT 1)); "

            for (let c = 0; c < products[i].images.length; c++) {
                // Ad all images to query
                query += "INSERT INTO `tImage`(`Url`) VALUES ('" + products[i].images[c].src + "'); " +
                    "INSERT INTO `rtImage`(`ProductId`, `ImageId`) " +
                    "VALUES ((SELECT `Id` FROM `tProduct` WHERE sku = '" + products[i].sku + "' LIMIT 1), " +
                    "(SELECT `Id` FROM `tImage` WHERE url = '" + products[i].images[c].src + "' LIMIT 1)); "
            }
        }
    }

    query = query.replace(/[\n\r\t]/g,);

    if (query != "") {
        // Create new Product in DB 
        return new Promise((resolve, reject) => {
            connection.query(query, function (error, results, fields) {
                if (error) { console.log(error) };
                console.log('INSERTING successful');
                resolve(results);
            });
        });
    } else {
        console.log('There are no new products to insert in db');
    }
}

如果我执行console.log(query) (在我的数据库上执行查询之前)并直接在php myadmin中执行字符串，一切正常，但是如果我在像connection.query(query, function (error, results, fields).....这样的代码中执行查询，我会得到几个错误。

终端中出现错误消息：

code: 'ER_PARSE_ERROR',
  errno: 1064,
  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 'INSERT INTO `tDescription`(`Description`, `Language`) VALUES ('<p><strong>Tantra' at line 1",
  sqlState: '42000',
  index: 0,

我还得到了由于错误而在终端中返回的sql查询，如果我直接在php myadmin中执行这个查询，我也会得到一个错误->。

SQL query: Documentation


INSERT INTO `rtImage`(`ProductId`, `ImageId`) VALUES ((SELECT `Id` FROM `tProduct` WHERE sku = 'H1500148' LM 
IT 1), (SELECT `Id` FROM `tImage` WHERE url = 'https://cdnbigbuy.com/images/H1500148_409897.jpg' LIMIT 1))
MySQL said: Documentation

#1064 - 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 'LM
IT 1), (SELECT `Id` FROM `tImage` WHERE url = 'https://cdnbigbuy.com/images' at line 1

它看起来就像是以某种方式划分了...use near 'LM IT 1)....

我希望你能理解问题所在，也许有人能给我一些建议。

Answer 1

您的查询将被处理为“LIMIT”，这只是控制台中出现错误的新行。

在任何情况下，您都不应该对SQL查询使用字符串连接(甚至模板文字)，因为1.这很可能是问题的根源。2.非常危险，因为它允许SQL注入攻击。

请改用参数。下面是一个例子：

connection.query("SELECT * FROM bank_accounts WHERE dob = ? AND bank_account = ?",[
     req.body.dob,
     req.body.account_number
    ],function(error, results){});

阅读有关SQL注入和占位符read this article.的更多信息

Answer 2

谢谢你的有用的提示。

问题是我没有在代码中设置multiple statements: true。默认情况下，此var为false，并且应该为true，否则不可能一次请求执行多个查询！