How do I insert a variable into a mysql LIKE query?

Question

I have recently been desgining a back end web server that is primarily used to manipulate data from a mysql database. The server is designed in a DOM format, with a controller folder and an app.js

I have implemented a few GET, POST PUT requests in such a format

This is what one GET request looks like

 app.get("/movies/:movieID/", (req, res, next) => {
      const movieID = parseInt(req.params.movieID);
      // if userID is not a number, send a 400.
      if (isNaN(movieID)) {
        res.status(400).send();
        console.log("Error has occured with getting movieID")
        return;
      }
    
      movies.findByID(movieID, (error, movie) => {
        if (error) {
          res.status(500).send();
          console.log("get Id has error")
          return;
        };
        
        if (movie === null) {
          res.status(404).send();
          console.log("movie ID is invalid")
          return;
        };
        
        //console.log(movie)
        res.status(200).send(movie);
        console.log("Successful movie ID")
      });
    });

This is inside the controller folder

,findByID: function (movieID, callback) {
        var dbConn = db.getConnection();
        const findMovieByIDQuery = "select * from movies left join genres on movies.genreid = genres.genreid left join reviews on reviews.movieid = movies.movieid where movies.movieid = ?;";
        dbConn.connect(function (err) {
           if (err) {
              console.log(err);
              return callback(err, null);
           } else {
               dbConn.query(findMovieByIDQuery, [movieID], (error, results) => {
                   dbConn.end();
                   if(error) {
                       return callback(error, null);

                   } 
                   else if (results.length === 0) {
                    callback(null, null);
                    return;
                };

                console.log(results)
                return callback(null, results[0]);
            });
        }
    });
}

How ive implemented it is to take the users input, in this case a number and input it in the query based on the ? position.

However, I am now trying to create on whereby the user is able to search based on a string not just an ID number.

This is my code so far

app.get("/movies/:movieKeyword", (req, res, next) => {
  console.log("Reached the app.js")
  movies.findByKeyword((error, moviesAvailable) => {
    if (error) {
      console.log(error);
      res.status(500).send();
    };
    res.status(200).send(moviesAvailable);  
  });
});


,findByKeyword: function(callback) {
            console.log("Reached find by keyword")
            var dbConn = db.getConnection();
            const findAllMoviesQuery = "SELECT title, description, cast, time, opening_date, picture from movies WHERE title LIKE '% ? %';"
            dbConn.connect(function (err){
                dbConn.query(findAllMoviesQuery, (error, results) => {
                    if (error) {
                        return callback(error, null);
                    };
                    console.log(results)
                    return callback(null, results);
                });
            });
        }

To use the LIKE query in mysql, I need the variable to be stored in this format: "% ? %"

However, I am not able to get this query to work as the program is not able to insert the variable into the ? this time as it is within 2 quotation marks and 2 percentage symbols

Your code doesn't even attempt to insert the variable value? Which mysql package are you using? — ChrisG, Aug 04 '21 at 07:49
Anyway, the solution is probably to insert `dbConn.escape(keyword)` inbetween the % symbols using string composition. — ChrisG, Aug 04 '21 at 07:55
What have you tried so far? Where are you stuck? Also, please do not use irrelevant tags - otherwise explain how this question is related to [tag:server], [tag:mysql-workbench], or [tag:backend] — Nico Haase, Aug 04 '21 at 08:45

ChrisG · Answer 1 · 2021-08-04T08:41:10.890

0

Here's a solution that seems injection safe and works for me:

const keyword = "Test";
const escaped = keyword.replace(/[^a-z0-9]/gi, ""); // prevent injection
const like = `%${escaped}%`;                        // add percentage symbols
const query = "SELECT * FROM movies WHERE `title` LIKE ?";

conn.query(query, [like], function (err, rows) {
    if (err) throw err;
    else console.log(rows);
});

I found no way to use library functions to escape the search term, so I simply replace all non-alphanumeric characters. Then I add the percentage signs and insert it into the query like any other string.

Further reading: MySQL Injection by LIKE operator

edited Aug 04 '21 at 08:41

answered Aug 04 '21 at 08:26

ChrisG

8,206
4
20
34

I tried to look for existing answers about `LIKE` queries and found nothing. It's all about inserting a string in general. By simply adding the percentage symbols to the search term, it's basically just that: inserting a string into a query. I decided to post an answer but I'm happy to remove it if a suitable duplicate is pointed out to me. – ChrisG Aug 04 '21 at 08:28

score -1 · Answer 2 · answered Aug 04 '21 at 07:49

-1

I think you can use Template Literals. Here is a resource, Template Literals. You can remove the percentage signs and replace them with this syntax ${variable_name}

answered Aug 04 '21 at 07:49

Viki

124
6

How do I insert a variable into a mysql LIKE query?

2 Answers2