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 }

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 }

• javascript
• mysql
• node.js

• 1 What is the actual error that you are getting? (What did console.log(err); show?) Also, please read this xkcd immediately. – 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 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 Commented Apr 12, 2017 at 3:46
• placement of closing '% should be %' – Hamza Rashid Commented Apr 12, 2017 at 3:51

3 Answers 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 ment, 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.

Simple way to do it:

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

I think this is much cleaner.

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 :)