CODEWITHSUNDEEP
javascriptmysqlnode.js
정성진
정성진

Reputation: 79

Can anyone tell me why this occur MySQL syntax error?

app.get('/delete_server',function(req,res){
    var before_page = req.query.before;
    var title = req.query.title;
    var sql = 'delete from ? where title=?';

    conn.query(sql,[before_page,title],function(err,rows,fields){
        if(err) console.log(err);
        else {
            console.log(rows[0]);
            res.redirect('/menu/'+before_page);
        }
    })
})

I'm trying to make deleting server using querystring and sql query. But it doesn't work. Console say it's SQL syntax error. I think the problem is before_page variable. SQL query become delete from 'japan' where title='test' I think the 'japan' is wrong.

What should I do?

Upvotes: 0

Views: 42

Answers (2)

axiac
axiac

Reputation: 72226

Behind the scenes, the JavaScript library uses MySQL prepared statements to run your query. The documentation of the PREPARE MySQL statement explains:

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

This means you cannot use ? as a placeholder for command names (SELECT, DELETE etc), MySQL keywords (FROM, AND etc) or functions (MIN(), SUBSTR() etc), database names, table names or field names. Only the values can be replaced this way.

The solution for your code is:

var sql = 'delete from `'+before_page+'` where title=?';

conn.query(sql, [title], function(err, rows, fields) {
   // ...
}

Or, with ES5 and newer, you can use template literals:

var sql = `delete from \`${before_page}\` where title=?`;

You have to be sure that the value of before_page is a valid table name. If you get it from the request your code is wide open to SQL injection.

Check the input value against a whitelist of table names or, even better, do not leak the table names outside the application; use generic names for the entities you handle and keep a mapping between the public name and the table name.

Find the table name by looking up the value of req.query.before into this map. Reject the operation if the input value is not in the map.

Upvotes: 1

정성진
정성진

Reputation: 79

I changed variable 'sql' to:

'delete from '+before_page+' where title=?'

Upvotes: 0

Related Questions