MySQL automatic apostrophes any ideas?

Question

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);
});
});

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 } });
};

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

I found [this question](https://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements) and 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. — , Jan 10 '21 at 15:40
Thank you it solved the problem. Okay I ll look into that, this is my first SQL project :) — MadGrip, Jan 10 '21 at 15:47

score 0 · Accepted Answer · answered Jul 27 '21 at 12:23

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.

MySQL automatic apostrophes any ideas?

1 Answers1