SELECT query from mysql with node js (LIKE query)

Question

I'm using node js to develope my project, i use mysql to store my data.

I have a problem when i select with like query, and it give an error like this:

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 'test'%' ORDER BY create_time DESC LIMIT 0,10' at line 1

SELECT * FROM user WHERE fullname LIKE N'%'test'%' ORDER BY create_time DESC LIMIT 0,10

I know error here is 'test' in query, but it's a string, i can't remove it, my code here:

data = {};
data.fullname = 'test';
data.start    = 0;
data.limit    = 10;
let getFullname = function (data, callback) {
   return db.query("SELECT * FROM user WHERE fullname LIKE N'%?%' ORDER BY create_time DESC LIMIT ?,? ", [data.fullname,data.start,data.limit], callback);
}

How can i solve my problem, thank you!

Answer 1

You are right, problem is mysql package add single quote ', you can use following manner

   return db.query(`SELECT * FROM user WHERE fullname LIKE N'%${data.fullname}%' ORDER BY create_time DESC LIMIT ?, ? `, [data.start,data.limit], callback);

or

data.fullname = '%' + data.fullname + '%';
return db.query("SELECT * FROM user WHERE fullname LIKE N? ORDER BY create_time DESC LIMIT ?,? ", [data.fullname,data.start,data.limit], callback);