Passing in a variable to a query string using node/js/MySQL workbench

Question

Problem: I am receiving a null insert into my db when passing the variables into the insert statement. Variables are firstName, lastName, email, passW.

What i know: I know that I am getting the user input form data back. And I am consoling the variables they are stored to before the insert statement which works and prints the form data to the console. When the form is submitted on the web page, it stores the "firstName", "LastName", "email", "passW" which are all variables. But when i pass them into MySQL Workbench, they are posting as null. My conclusion is that the variables are not being passed in correctly, but when i try suggestions from this page and the other pages, it throws a syntax error that the syntax for SQL is incorrect. I tried to add as much info as i can, if you need anymore that im not providing or need me to post test results, LMK.

Code


var express = require("express");
var http = require("http");
var mysql = require("mysql");
var express = require("express");
var path = require("path");

var app = express();
var PORT = 3001;

// Sets up the Express app to handle data parsing
app.use(express.urlencoded({ extended: false }));
app.use(express.static(path.join(__dirname, 'public'))); // testing
app.use(express.json());
app.use("/assets", express.static("assets"))


// home page route
app.get("/", function(req, res) {
    res.sendFile(path.join(__dirname, "index.html"));
  });
// create account route
app.get("/create", function(req, res) {
  res.sendFile(path.join(__dirname, "create.html"))
});
// login route
app.get("/login", function(req, res) {
  res.sendFile(path.join(__dirname, "login.html"))
});


// DB Connection
var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "sportsCorner",
  port: 3306,
});
// connection response 
connection.connect(function (err) {
  console.log("SQL connected as id " + connection.threadId)
});  


// Takes the data from our login form
app.post('/handler', function (req, res) {


  // user input from the forms
  var firstName = req.body.firstName;
  var lastName = req.body.lastName;
  var email = req.body.email;
  var passW = req.body.password;

  // testing our responses stored in the variables 
  console.log("F: " + firstName, "L: " + lastName, "E: " + email, "P: " + passW)

  connection.query("INSERT INTO loginInfo VALUES(firstName, lastName, email, passW)", function (err, res) {
    if (err) throw err;
    console.log("Inserted ...")
  });

  // Getting login info from DB
  connection.query('SELECT * from loginInfo', function (err, res) {
    if (err) throw err;
    console.log(res)
    console.log("Response ...")
  })
});


// Start Server
app.listen(PORT, function() {
  console.log("Server listening on: http://localhost:" + PORT);
});
   </script>

<HTML> (FORM)
 <form method="POST" action="/handler">
   <input type="text" name="firstName" placeholder="First Name">
   <input type="text" name="lastName" placeholder="Last Name">
   <input type="email" name="email" placeholder="Email">
   <input type="password" name="password" placeholder="Password">
   <input type="submit" />
 </form>
</HTML>

Thanks for your help in advance

You might want to read the docs for mysql-node more closely. https://stackoverflow.com/questions/21779528/insert-into-fails-with-node-mysql — elliottregan, Feb 12 '20 at 00:04

score 1 · Accepted Answer · answered Feb 11 '20 at 23:09

Use this as insert query

var sql = `INSERT INTO loginInfo 
            VALUES
            (
                ?, ?, ?, ?
            )`;
connection.query(sql, [firstName, lastName, email, passW], function (err, res) {
    if (err) throw err;
    console.log("Inserted ...")
  });

score 0 · Answer 2 · answered Feb 11 '20 at 23:46

Hello I have debuged your code as follow

var express = require("express");
var http = require("http");
var mysql = require("mysql");
var express = require("express");
var path = require("path");

var app = express();
var PORT = 3001;

// Sets up the Express app to handle data parsing
app.use(express.urlencoded({ extended: false }));
app.use(express.static(path.join(__dirname, 'public'))); // testing
app.use(express.json());
app.use("/assets", express.static("assets"))


// home page route
app.get("/", function (req, res) {
    res.sendFile(path.join(__dirname, "index.html"));
});
// create account route
app.get("/create", function (req, res) {
    res.sendFile(path.join(__dirname, "create.html"))
});
// login route
app.get("/login", function (req, res) {
    res.sendFile(path.join(__dirname, "login.html"))
});


// DB Connection
let connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "sportsCorner",
    port: 3306
});

// connection response 
connection.connect(function (err) {
    if (err) throw err;
    console.log("Connected to MySQL database!");
});

// Takes the data from our login form
app.post('/handler', function (req, res) {


    // user input from the forms
    var firstName = req.body.firstName;
    var lastName = req.body.lastName;
    var email = req.body.email;
    var passW = req.body.password;

    // testing our responses stored in the variables 
    console.log("F: " + firstName, "L: " + lastName, "E: " + email, "P: " + passW)

    let queryAddUser = `INSERT INTO loginInfo (firstName, lastName, email, passW) \
        VALUES (?, ?, ?, ?)`;

    // Protect your query from SQL attacks
    let preparedQuery = connection.format(queryAddUser, [firstName, lastName, email, passW]);

    // Execute the query
    connection.query(preparedQuery, function (error, result) {
        if (error) throw error;
        console.log('QUERY ADD NEW USER EXECUTED SUCCESSFULLY', result);
    });

    // Query count users
    let queryCountUsers = "SELECT count(*) as totalUsers FROM `loginInfo`";

    // Execute the query
    connection.query(queryCountUsers, function (error, result) {
        if (error) throw error;
        console.log('QUERY queryCountUsers EXECUTED SUCCESSFULLY', result);
    });
});


// Start Server
app.listen(PORT, function () {
    console.log("Server listening on: http://localhost:" + PORT);
});

I add the prepare query statement connection.format() for protection against SQL injections attacks.

Maksym Bezruchko · Answer 3 · 2020-02-11T23:30:58.123

-1

Try to change this:

connection.query("INSERT INTO loginInfo VALUES(firstName, lastName, email, passW)", function (err, res) {
    if (err) throw err;
    console.log("Inserted ...")
  });

to this:

 connection.query(`INSERT INTO loginInfo VALUES('${firstName}', '${lastName}', '${email}', '${passW}')`, function (err, res) {
    if (err) throw err;
    console.log("Inserted ...")
  });

edited Feb 11 '20 at 23:30

answered Feb 11 '20 at 22:58

Maksym Bezruchko

1,223
6
23

Thanks for such a quick response. I gave it a try and got a new error code. Ill add it below ```Error: ER_PARSE_ERROR: 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 '@test.com, tester)' at line 1``` – Corbin Brockbank Feb 11 '20 at 23:25
Ah, yes. I've edited the answer. Could you check it again? – Maksym Bezruchko Feb 11 '20 at 23:31
This allows for SQL injection. Don't do this! – JadeSpy Dec 22 '22 at 18:56

Passing in a variable to a query string using node/js/MySQL workbench

3 Answers3

Linked

Related