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!

Arif Khan · Accepted Answer · 2018-05-25 04:58:34Z

5

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

answered May 25, 2018 at 4:58

Arif Khan

5,0592 gold badges19 silver badges29 bronze badges

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

3 Comments

Social Code Over a year ago

Yes, the second way work perfectly, because i must use prepare stament for secure my app

Hiren Over a year ago

Can anyone explain what is the N in the N? part of the LIKE operation?

Arif Khan Over a year ago

@Hiren N? means the string must start with the N character

LookWorld · Accepted Answer · 2020-12-21 12:50:36Z

2

CONCAT("%", ? , "%")

:)

this is part of my code :

########## 

where += ` AND ( titre LIKE CONCAT("%", ? , "%") OR resume LIKE CONCAT("%", ? , "%") ) ` ; 
vals.push(dataRes.rech) ; 
vals.push(dataRes.rech) ; 

############# 

sql = `SELECT #########  WHERE 1=1 ${where}` ;  

connection.query( sql , vals , async function(err, services, fields) { 
if(err) rej({er : 1 , code : err , sql: sql , vals : vals}) ; 
else{ res({er : 0 , data : services }) }

edited Dec 21, 2020 at 12:50

answered Oct 27, 2020 at 8:39

LookWorld

413 bronze badges

2 Comments

jlapoutre Over a year ago

Better to supply the complete context for your solution, at least the return db.query() part so it is perfectly clear where and how to insert this concatenation. Did you test this code?

LookWorld Over a year ago

this is part of the code : ########## where += ` AND ( titre LIKE CONCAT("%", ? , "%") OR resume LIKE CONCAT("%", ? , "%") ) ` ; vals.push(dataRes.rech) ; vals.push(dataRes.rech) ; ############# sql = SELECT ######### WHERE 1=1 ${where} connection.query( sql , vals , async function(err, services, fields) { if(err) rej({er : 1 , code : err , sql: sql , vals : vals}) ; else{ res({er : 0 , data : services }) }

Collectives™ on Stack Overflow

SELECT query from mysql with node js (LIKE query)

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related