CODEWITHSUNDEEP
mysqlnode.js
adit
adit

Reputation: 33644

unexpected identifier on a mysql query in nodejs

I have the following code:

 connection.query("SELECT * FROM accounts_data WHERE new_username LIKE '%" + username + "%';", function (error, results, fields) {

});

and I am getting the following error

SELECT * FROM `accounts_data` WHERE `new_username` LIKE '%preloved_bys%'
                               ^^^^^
SyntaxError: Unexpected identifier
    at wrapSafe (internal/modules/cjs/loader.js:1054:16)
    at Module._compile (internal/modules/cjs/loader.js:1102:27)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1158:10)
    at Module.load (internal/modules/cjs/loader.js:986:32)

what is wrong with the query? when i execute it via phpmyadmin it works just fine

Upvotes: 1

Views: 1545

Answers (5)

kartik tyagi
kartik tyagi

Reputation: 7150

It seems there is nothing wrong with the following code.
but fyi, SyntaxError: Unexpected identifier occurs due to some reasons, mentioned below:

  1. SQL is case-sensitive language when you pass any parameter into it like 'new_username'. Recheck it should be correct.
  2. use of unwanted special character like ' or "
  3. using an invalid identifier.

    Give a try with the following code:
    let str = '%'+username+'%';
    let sql = "SELECT * FROM accouts_data WHERE new_username LIKE ?";
        
        connection.query(sql, str, function(err, result) {
            if(err) throw err
        });

Just in case, if it doesn't work, update your node_modules with `npm install`. Or just remove version number from all modules in `package.json` file like `"mysql": "^2.18.1"` just remove `^2.18.1`. and Then, Run:
npm install


It'll update every module with their latest version automatically.

Upvotes: 2

Δ O
Δ O

Reputation: 3710

I think you should use proper escaping provided by mysql lib:

connection.query(
  "SELECT * FROM accounts_data WHERE new_username LIKE ?; ",
  ['%' + username + '%'],
  function (error, results, fields) { ... }
);

Also check your mysql version both on client (npm package) and server side.

For debugging, I often use console.log(connection.format(query, values));

Upvotes: 1

shivam dhankani
shivam dhankani

Reputation: 99

You can use concat example 

connection.query("SELECT * FROM accounts_data WHERE new_username LIKE concat('%',?,'%');", [username],(error, results, fields)=>{console.log(result)})

Upvotes: 0

Bhautik Domadiya
Bhautik Domadiya

Reputation: 100

i get success with do following : 

var value = 'sc188203395acc255';
connection.query('SELECT * from emp where empid like ?', '%' + value + '%', ...)

Turn on debugging in the driver (pass debug:true as argument to mysql.createConnection ), it doesn't escape the percent sign

One More Way to Slove this Error :

"SELECT * FROM emp WHERE name empid " + connection.escape('%'+value+'%')

use connection.escape alternate way to do same things

One More Way to Solve this Error :

reinstall module on your project because sometimes error can be solved by reinstalling it

delete node_modules and package-lock.json and last run npm install

Upvotes: 2

mousto090
mousto090

Reputation: 2039

Nothing wrong with the query, tested it with mysql and mysql2 and it gives the expected result. It must be related to nodejs, try removing node_modules directory and package-lock.json file and run npm install

Upvotes: 1

Related Questions