CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
Mervius
Mervius

Reputation: 35

SQL syntax error when updating record via NodeJS with Express and MySQL modules

I'm getting an "app crashed" error in Visual Studio Code's terminal when I try to update a MySQL record in NodeJS.

app2.js:

const express = require('express');
const mysql = require('mysql');

// create connection
const db = mysql.createConnection({
    host: 'localhost', 
    user: 'root',
    password: '',
    database: 'journey_test'
})

 // connect to mysql
 db.connect(err => {
     if(err) {
         throw err
     }
     console.log('MySQL Connected');
 })

 const app = express()

 app.use(express.static('public'));
 app.set('view engine', 'ejs');

// this pulls index.ejs to the root folder location of the site
app.get('/', function (req, res) {
    res.render('index2');
});

app.post('/change/:artist/:id', (req, res) => {

        let newArtist = req.params.artist;
        let newID = req.params.id ;
    // even hard-setting variables in my query causes failure
     let sql = `UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'`
        let query = db.query(sql, err => {
            if(err) {
                throw err
            }
            res.send('Entry updated');
        })
    })


 app.listen('3000', () => {
     console.log('Server Started on port 3000')
 })

index2.ejs:

<!DOCTYPE html>
<html>
<head>
        <script>
           function testing() {
              var xhttp = new XMLHttpRequest();
              xhttp.onreadystatechange = function() {
              const text = this.responseText;
              const obj = JSON.parse(text);          
                     document.getElementById("Hello2").innerHTML = "Yes!";
         };
              xhttp.open("POST", "change/The Cure/104", true);
              xhttp.send();
         }
         </script>
    </head>
    <body>
        
        <button style= "height:22px";" type="button" onclick="testing()">Update</button> 
        <div id="Hello2">Did I do it?</div>
             
    </body>
</html>

The error in Powershell:

PS C:\Users\Mervius\Documents\NodeJS> nodemon app2.js
[nodemon] 2.0.12
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node app2.js`
Server Started on port 3000
MySQL Connected
C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

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 'UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'' at line 1
    at Query.Sequence._packetToError (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Parser.js:433:10) 
    at Parser.write (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Protocol.js:38:16)     
    at Socket.<anonymous> (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\Connection.js:88:28)        
    at Socket.<anonymous> (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\Connection.js:526:10)       
    at Socket.emit (events.js:375:28)
    at addChunk (internal/streams/readable.js:290:12)
    --------------------
    at Protocol._enqueue (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\protocol\Protocol.js:144:48) 
    at Connection.query (C:\Users\Mervius\Documents\NodeJS\node_modules\mysql\lib\Connection.js:198:25)
    at C:\Users\Mervius\Documents\NodeJS\app2.js:36:24
    at Layer.handle [as handle_request] (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\layer.js:95:5)
    at next (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\route.js:137:13)
    at Route.dispatch (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\route.js:112:3)        
    at Layer.handle [as handle_request] (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\layer.js:95:5)
    at C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\index.js:281:22
    at param (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\index.js:354:14)
    at param (C:\Users\Mervius\Documents\NodeJS\node_modules\express\lib\router\index.js:365:14) {
  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 'UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "UPDATE tblbillboardcomplete SET artist = 'The Cure' WHERE id = '104'"
}
[nodemon] app crashed - waiting for file changes before starting...

Query that works in MySQL Console: You can see it works.

Using WAMP server (because I'm rebuilding a previous Visual Basic WinForms app as a web app): MySQL: 5.7.9 Apache: 2.4.17

I swear the above worked just a few days ago, but the environment may have changed a little in that time (e.g. installing more node modules, that kind of thing, though I was definitely using the WAMP server at the time).

Any help appreciated.

Edit: I'm experiencing no problem selecting from or inserting into the MySQL database using NodeJS.

ADDED (after John Michael Manlupig's suggestion initially worked, at least in the original context): The problem persists in another spot. I'm posting info (as "result") to app.js via a tinymce rich-text editor, but it fails out the same way when using the code below:

const express = require('express');
const mysql = require('mysql');
//const dayjs = require('dayjs');
const multer = require('multer');
const upload = multer({ dest: 'uploads/' });

app.post('/result', upload.none(), function (req, res, next) { // when we save RTE info in tinymce
    var newEntry = req.body.content
    var newDate = activedate
    const sql = `UPDATE main SET entry = ? WHERE dateID = ?`;
    const query = db.query(sql, [newEntry, newDate], err => {
        if(err) {
            throw err
        }
        res.send('Entry updated');
        })
    });

I can send the received value of "req.body.content" to the console, and it's good, but sending it through the rest of the code causes "app crashed" in the same way as described above, despite parametizing as suggested by @John Michael Manlupig, and despite the query text containing correct/expected info, and being useable as-is in MySQL Console.

Anything obvious I'm overlooking?

Upvotes: 2

Views: 707

Answers (2)

Mervius
Mervius

Reputation: 35

I think I've identified the ghost in the machine. I recently started saving useful blocks of code in OneNote, and was then recently copying-n-pasting from OneNote into Visual Studio Code. Visual Studio wasn't telling me anything was wrong, and lots of it worked, but it looks strongly like there was invisible junk in there (at least some of the time). Ugh!

Thanks for all the help, as it was a necessary part of the solution!

Upvotes: 0

John Michael Manlupig
John Michael Manlupig

Reputation: 139

You should parametize the values instead of inserting it directly into the string.

const sql = `UPDATE tblbillboardcomplete SET artist = ? WHERE id = ?`;
const query = db.query(sql, ['The Cure', 104], err => {
    if(err) {
        throw err
    }
    res.send('Entry updated');
});

Upvotes: 1

Related Questions