How to pass Javascript variables in MySQL query

Question

I've done some research and I'm getting conflicting answers, so here it goes. Do you have to pass JS variables into PHP first or can you insert them directly into a MySQL table? I have this running in node.js, thanks!

var part = [];
var des = [];
var price = [];
var request = require('request');
var cheerio = require('cheerio');
var mysql = require('mysql');

var con = mysql.createConnection({
  host: "127.0.0.1",
  user: "root",
  password: "",
  database: "parts"
});

request('URL', function(error, response, body) {
    if (!error && response.statusCode == 200) {
        var $ = cheerio.load(body);
        getPartNumber();
        getDescription();
        getPrice();
        con.connect(function(err) {
        if (err) throw err;
        for (var i = 0; i<part.length; i++){
            var sql = "INSERT INTO data_9_17 (partNumber, description, price) VALUES (part[i], des[i], price[i])";
            con.query(sql, function (err, result) {
                if (err) throw err;
                console.log("1 record inserted, ID: " + result.insertId);
            });
        }
});

function getPartNumber() {
$("input[name = 'sku']").each(function() { part.push($(this).val()) });
}

function getDescription() {
$(".ellipsis_text").each(function() { des.push($(this).text()) });
}

function getPrice() {
$(".sellprice, .dbl").each(function() { price.push($(this).text()) });
}
}
});

But not that way...remember that with node you have server side javascript... — Hackerman
– Hackerman, Commented Sep 8, 2017 at 16:58

kangaro0 · Accepted Answer · 2017-09-08 17:19:11Z

3

your query string should go like this:

"INSERT INTO data_9_17 (partNumber, description, price) VALUES ('" + part[i] + "', '" + des[i] + "', '" + price[i] + "')";

You can concenate values by using +. Check this out.

Edit: I made a mistake. Didn't put ' in. Fixed now... Anyways, J Johnson is right. It's better to use Prepared Statements because of SQL Injection.

edited Sep 8, 2017 at 17:19

answered Sep 8, 2017 at 17:01

kangaro0

1851 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J Johnson · Accepted Answer · 2017-09-08 17:05:33Z

1

You can use prepared statement, like this:

var query = 'INSERT INTO data_9_17 (partNumber, description, price) VALUES (?, ?, ?)';

con.query(query, [part[i], des[i], price[i]], function(err, results) ... )

// You will put your variables inside []

If you want more information, refer to the mysql npm module:

https://www.npmjs.com/package/mysql#escaping-query-values

answered Sep 8, 2017 at 17:05

J Johnson

1451 silver badge15 bronze badges

2 Comments

Garrett Pe Over a year ago

I only intend to run this on my desktop, but I totally get what you are saying. Thanks, J Johnson!

J Johnson Over a year ago

No problem @GarrettPenfield :)

Maneesh Manoj · Accepted Answer · 2020-03-07 11:55:32Z

0

You need to concatenate your query string with the values of the variables:

var sql = "INSERT INTO data_9_17 (partNumber, description, price) VALUES (" + part[i] + ", " + des[i] + ", " + price[i] + ")";

For security reasons, I recommend you to use the prepared statement:

var sql = mysql.format("INSERT INTO data_9_17 (partNumber, description, price) VALUES (?, ?, ?)", (part[i], des[i], price[i]));

con.query(sql, function (err, result) {
.....
.....
.....
}

edited Mar 7, 2020 at 11:55

Maneesh Manoj

33 bronze badges

answered Sep 8, 2017 at 17:27

Juani Calle Machado

263 bronze badges

Collectives™ on Stack Overflow

How to pass Javascript variables in MySQL query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related