MySQL automatic apostrophes any ideas?

Im trying to patch a value in my Database, but Im getting an error.

Backend:

router.patch("/toggleState", (req, res) => {
const todoId = req.body.todoId;
const attribute = req.body.attribute;
const newValue = req.body.newValue;
const sql = "UPDATE posts SET ? = ? WHERE todo_id = ?";

db.query(sql, [attribute, newValue, todoId], (err, result) => {
    if (err) throw err;
    res.send(result);
});
});

​x
 
router.patch("/toggleState", (req, res) => {const todoId = req.body.todoId;const attribute = req.body.attribute;const newValue = req.body.newValue;const sql = "UPDATE posts SET ? = ? WHERE todo_id = ?";​db.query(sql, [attribute, newValue, todoId], (err, result) => {    if (err) throw err;    res.send(result);});});​    ​

Frontend:

const toggleState = (todo_id, attribute, newValue) => {
    if (userId) {
        console.log("ATTRIBUTE: ", attribute);
        //Axios.patch(`${apiUrl}/todo/toggleState`, { todoId: todo_id, attribute: attribute, newValue: newValue });
    }
    dispatch({ type: actionTypes.toggleState, payload: { todo_id, attribute } });
};

 
const toggleState = (todo_id, attribute, newValue) => {    if (userId) {        console.log("ATTRIBUTE: ", attribute);        //Axios.patch(`${apiUrl}/todo/toggleState`, { todoId: todo_id, attribute: attribute, newValue: newValue });    }    dispatch({ type: actionTypes.toggleState, payload: { todo_id, attribute } });};​

Error: “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ”important’ = 1 WHERE todo_id = 1′ at line 1″

SQL after processed: sql: “UPDATE posts SET ‘important’ = 1 WHERE todo_id = 1”

I noticed that there are ‘ ‘ apostrophes around my attribute variable (important) and I think this is causing the error. Hovever Ive checked that variable in front end and back end and there are no apostrophes when I console.log it, and variable is typeof string. Any ideas?

Also when I try the same sql string in mysql-workbench but instead of variables I put real values it works.

Also Im running react/axios on front end and Node/Express on back end with mysql if that helps

Answer

A quick fix is to use ?? for the column name. During preparing a statement you’re not supposed to dynamically insert column names though, which is the reason why you have to use this workaround. It’s about preventing SQL injection where a user sends off a manipulated query that changes an arbitrary column. A better solution is to pick a query string based on the attribute, you can automate this by looking up the supplied attribute in an array of queries.

Advertisement

Answer