mysql.format what should I change null variables to if I want to make it accept any value in the que-CodePudding

I am trying to do a query with many different parameters but not all are necessary since it will act as a way to search through a database. I could just do this

if(req.query.Player1 === ''){
    var Player1 = " Player1 = "   "'"   req.query.Player1   "'"; 
}

However, this is not very secure so I wanted to use mysql.format so it makes it more secure.

var sql = mysql.format("SELECT * FROM sg_vod_database.vod_data WHERE Player1 = ?", [Player1])

Here is the other option I have tried. However if the value is null because they didn't enter anything, I want the query to accept all values for that item. If they don't care who the player is then it should be okay with any player. But I cannot find a way to make it so that the mysql.format will accept a variable that turns it to select all values. I had tried replacing it var Player1 with *, 1 1, Player1, and true but the format wraps it in single quotes by default so it is not very effective. If there is a way to maintain this and have it accept values or a different way to format it so it remains secure please let me know

CodePudding user response：

Hey maybe you can predefine your query, and only set it when a user provides input. I'd say maybe you can try experimenting with "LIKE"

// untested
query = "%"

if(req.query.Player1){
    query = req.query.Player1
}

var sql = mysql.format("SELECT * FROM sg_vod_database.vod_data WHERE Player1 LIKE ?", [query])

P.S. I suggest you define the columns you want returned from your DB specifically rather than using asterisk(*), unless that was just for your question/example. Using asterisk makes the application unaware of schema changes.

EDIT: I hope I don't misunderstand the question. If the user does not provide input then the SQL would return all rows in the table.

CodePudding user response：

Here is my solution

//If it's null, then set it the value to the same name as the column so it returns all values
if(Player1 === ''){
    Player1 = 'Player1'
}
//Else, wrap the name in " " so that it is correctly read in the query
else{
    Player1 = '"'   Player1   '"'
}

if(Player2 === ''){
    Player2 = 'Player2';
}
else{
    Player2 = '"'   Player2   '"'
}


var sql = mysql.format(`SELECT * FROM sg_vod_database.vod_data WHERE Player1 = ${Player1} AND Player2 = ${Player2}`)