2

In node js I used to run a LIKE statement query to get data from MYSQL database, however, due to poor performances I have updated my query to use full-text search ("where match AGAINST" statement). I tried to run the query with a "?" placeholder or by using escape() method (to avoid sql injection) but with no success. The query only ran successfully without having the "?" placeholder or escape() method.

I have looked up other answers provided but couldn't find a solution to this.

code works - sql-injection vulnerable

function (req,res,next) { /// req.query.Name is coming from user input of a form const queryString = "Select idx, descr, price, product_img,\ stock, available from prod.product_list_details where match descr \ against" + "(" + "'" + req.query.Name + "'" + ")" connection.query(queryString, (err, rows, fields) => { if (err) { console.log("Failed to query for description: " + err) res.sendStatus(500) return } console.log("I think we fetched products successfully")

code doesn't work - added ? placeholder to avoid sql injection

function (req,res,next) { ///productDescription is from user input of a form var productDescription = "(" + "'" + req.query.Name+ "'" + ")" const queryString = "Select idx, descr, price, product_img, stock,\ available from prod.product_list_details where match descr against ?" connection.query(queryString, productDescription, (err, rows, fields) => { if (err) { console.log("Failed to query for description: " + err) res.sendStatus(500) return } console.log("I think we fetched products successfully")

Error message that I'm getting with second query:

Failed to query for description: Error: ER_PARSE_ERROR: 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 ''(\'Clorox\')'' at line 1

Is there any way to use mysql full-text search in node js and also have a way to avoid sql injection?

Thanks in advance for any suggestions!

EDIT Thanks for the answer, this is what worked for me.

function (req,res,next) { var userInput = req.query.Name ///values to search var modifiedUserInput = userInput.replace(/ /g,"+") var productDescription = "'+" + modifiedUserInput+ "'" const queryString = "Select idx, descr, price, product_img, stock, available from \ prod.product_list_details where match descr against (? IN BOOLEAN MODE)" connection.query(queryString, productDescription, (err, rows, fields) => { if (err) { console.log("Failed to query for description: " + err) res.sendStatus(500) return } console.log("I think we fetched products successfully")
Improve this question
5
  • + "'" + req.query.Name + "'" - this is sql-injection vulnerability! see: xkcd.com/327 Commented Apr 26, 2019 at 19:12
  • ` where match descr against ?` doesn't seem right. Do you mean WHERE descr MATCH ?? Commented Apr 26, 2019 at 19:47
  • 1
    @tadman here is the link to mysql syntax, thanks! - dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html Commented Apr 26, 2019 at 20:03
  • @alfasin - you meant even with "?" placeholder, this is sql-injection vulnerability, as shown in the second query? Commented Apr 26, 2019 at 20:06
  • 1
    @SnowKari Ah, I see what you're trying to do now. This would be a lot easier to read with the redundancy removed and with placeholders used instead of concatenation. Commented Apr 26, 2019 at 20:07

1 Answer 1

Reset to default
1

When using placeholders it's important to note that these are for data only and that syntax elements cannot be included, it will result in a parsing error.

In your case it means the query should be of the form:

WHERE MATCH (descr) AGAINST (? IN BOOLEAN MODE)

Where ? represents the data to be included, and only the data. When you put in the brackets it effectively expands to this:

WHERE MATCH (descr) AGAINST '(...)'

That breaks the syntax because the brackets are now inside the data instead of surrounding the data.

For things like LIKE where the % placeholders are part of the data, you put them in like this:

WHERE x LIKE ?

In other cases you can do:

WHERE x LIKE CONCAT('%', ?)

If you'd prefer to do the assembly database-side.

In any case, using prepared statements with placeholder values is extremely important so getting a handle on this is a big deal and it's great that you're working in that direction.

Hope that helps get you where you want to go.

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

3 Comments

Thanks for your response! Sorry for the confusion about concatenation (will edit my question). Yes, I'm trying to run the query based off user input of the form (req.query.Name). With the concatenation, it would give me ('Name'), so the query runs successfully without using "?" placeholder, it's the first query that i attached. However, when I tried to use with "?" placeholder, it returned an error. I'm trying to stay away from LIKE statement as the query takes a long time to run. Is there any other suggestions? Thanks so much!
I think you're missing the point here. Placeholders like ? are for data only. Any syntax elements need to be put into the prepared statement. So AGAINST ? will not work, imagine that expands to AGAINST '(...)' which is not valid. AGAINST (?) will.
It worked, thanks so much! really appreciate your explanation!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.