syntax error reported with valid insert statement

Jerome Mayeux

unread,

Nov 25, 2019, 12:44:31 PM11/25/19

to node-mysql

Hi all,

I've been trying to find the cause of this for weeks, (I tried the #node IRC channel but got tumbleweed)

If I run my insert statement in MySql Workbench

it works ok, if I run it from my node application I get:

You have an error in your SQL syntax;

connectivity is ok, as I can run select statements just fine.

the insert sql is:

use sys; insert into test (PersonName) values ('name value ');

The nodejs code is:

"use strict";

const PORT = process.env.PORT || 5000;
const express = require("express");
var people;
var mysql = require("mysql")
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'private-password**',
database: ''
})

connection.query("SELECT * from sys.test", function (err, rows, fields) {
if (err) throw err

console.log('data length = : '+ rows.length);
console.log('The query result is: '+ rows[0].personName);
people = rows;
});

const app = express();

app.get('/', function (req, res) {
res.send('GET request to the homepage')
})
// app.get('/api/peoplelist',(req,res) =>{
// res.json(people);
// });

// app.get('/api/peoplelist', function (req, res) {
// res.json(people)
// })

const bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: true }));
//app.use(express.static('wwwpub'))

//from docs
/*
app.post('/test', function (req, res) {
res.send('POST request to the test')
res.end;
})
*/
app.post('/api/addperson',(req,res) =>{
var newname = req.body.Name;
console.log("post route hit! with: ");
console.log( req.body);
const sql ="use sys; insert into test (PersonName) values ('"+ newname+ "');";
console.log("query: "+ sql);
connection.query(sql)
res.end;
});

app.listen(PORT);

console.log("server started on port: "+PORT);
ve been having the same issue for weeks and no idea why?

cheers,

Jerome.

ReplyForward

Ryan Lee

unread,

Nov 25, 2019, 4:35:21 PM11/25/19

to node-...@googlegroups.com

Hi,

The 'use sys;' means you are trying to run multiple statements at once. This is disabled by default for security reasons.

https://github.com/mysqljs/mysql#multiple-statement-queries

It is better to set the database as 'sys' on the connection and then do one query at a time. This adds a small level or protection against SQL injection attacks. Speaking of which, by not escaping your "newname" variable, you are prone to SQL injection.

connection.query('Insert into test set ?', { PersonName: newname }, (err) => {

if (err) { console.error(err); }

res.send('okay') //make sure the response end is inside the callback. Otherwise it might get terminated
});

--
You received this message because you are subscribed to the Google Groups "node-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-mysql+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/node-mysql/97fcc6e5-2051-4df9-9e48-91f17fbaf796%40googlegroups.com.

Jerome

unread,

Nov 25, 2019, 4:57:44 PM11/25/19

to node-...@googlegroups.com

Thank you SO much I’ve been unable to progress eith this task for weeks that makes sense

Much appreciated

Jerome.

Jerome Mayeux

Sent from Polymail

To view this discussion on the web visit https://groups.google.com/d/msgid/node-mysql/CACzR-%2BDV3YHO1gMcgPNg5srq-hrhj3HjM%3DoybDbMP5q9HHh5kw%40mail.gmail.com.

Jerome

unread,

Nov 25, 2019, 5:03:45 PM11/25/19

to node-...@googlegroups.com

Good point re: unprotected post value in sql

Though this will never go on the internet it’s just an internal task for education purposes