node.js mysql LIKE statement usage

Question

app.post('/like/:level/:name', function(req, res){
connection.query("SELECT * from books where " + req.params.level + " like '%" + req.params.name + "'%", function(err, rows, fields) {
if (!err){
var row = rows;
res.send(row);
console.log(req.params);
 console.log('The solution is: ', rows);}
else{
 console.log('Error while performing Query.');
console.log(err);}
});
});

Based on the above code can someone help me find the reason as to why I couldn't launch a query using the LIKE statement?

the error is shown as
    { Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%' at line 1
    at Query.Sequence._packetToError (/root/Newfolder/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Query.ErrorPacket (/root/Newfolder/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/root/Newfolder/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/root/Newfolder/node_modules/mysql/lib/protocol/Parser.js:75:12)
    at Protocol.write (/root/Newfolder/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/root/Newfolder/node_modules/mysql/lib/Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------
    at Protocol._enqueue (/root/Newfolder/node_modules/mysql/lib/protocol/Protocol.js:141:48)
    at Connection.query (/root/Newfolder/node_modules/mysql/lib/Connection.js:208:25)
    at /root/Newfolder/trial.js:98:12
    at Layer.handle [as handle_request] (/root/Newfolder/node_modules/express/lib/router/layer.js:95:5)
    at next (/root/Newfolder/node_modules/express/lib/router/route.js:137:13)
    at Route.dispatch (/root/Newfolder/node_modules/express/lib/router/route.js:112:3)
    at Layer.handle [as handle_request] (/root/Newfolder/node_modules/express/lib/router/layer.js:95:5)
    at /root/Newfolder/node_modules/express/lib/router/index.js:281:22
    at param (/root/Newfolder/node_modules/express/lib/router/index.js:354:14)
    at param (/root/Newfolder/node_modules/express/lib/router/index.js:365:14)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

What is the actual error that you are getting? (What did console.log(err); show?) Also, please read this xkcd immediately. — nnnnnn
– nnnnnn, Commented Apr 12, 2017 at 3:41
Also I am not trying to escape or something like that. I just want the query to return something such as when i input /title/a it will give me all data which starts with a as the title. — raymond017
– raymond017, Commented Apr 12, 2017 at 3:44
this is just a simple query as i am not trying to link it to the outside just a small project for a beginner — raymond017
– raymond017, Commented Apr 12, 2017 at 3:46

nnnnnn · Accepted Answer · 2017-04-12 03:50:46Z

3

You've got the single-quotes in the wrong place in your query string. Change this:

" like '%" + req.params.name + "'%"

...to this:

" like '%" + req.params.name + "%'"

...so that the second percent sign is inside the single quotes.

If you want do do a "starts with" search as mentioned in a comment, remove the '%' from the beginning of your field value:

" like '" + req.params.name + "'%"

And finally, not what you're asking, but you shouldn't directly concatenate user input into an SQL query.

edited Apr 12, 2017 at 3:50

answered Apr 12, 2017 at 3:48

nnnnnn

150k30 gold badges210 silver badges247 bronze badges

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

2 Comments

raymond017 Over a year ago

Does that mean escaping the queryinputs?

nnnnnn Over a year ago

Read the page I linked to (it has links to further articles) for background. Then this StackOverflow question covers SQL injection in Node.js.

Jean-François Corbett · Accepted Answer · 2018-10-07 15:31:28Z

1

Simple way to do it:

SELECT * from books where ${req.params.level} LIKE '${req.params.name}%'

I think this is much cleaner.

edited Oct 7, 2018 at 15:31

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

answered Oct 7, 2018 at 15:05

Hrishikesh Baidya

5874 silver badges8 bronze badges

Comments

Frederik Kliemt · Accepted Answer · 2023-02-02 23:32:52Z

1

In addition to @nnnnnn's answer: If somebody like me runs across this question and is wondering how to do what @nnnnnn did but by using parameters to have a safety measure against SQL Injections, here is what I found out:

connection.query("SELECT * from books where ? like ?", [req.params.level, "%" + req.params.name + "%"] function(err, rows, fields) { ... }

This query worked fine for me and made it possible to use the ?-placeholders and make use of parametrization :)

answered Feb 2, 2023 at 23:32

Frederik Kliemt

586 bronze badges

Collectives™ on Stack Overflow

node.js mysql LIKE statement usage

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related