I'm making a program where the user will enter data into inputs on a website. This information will then be relayed back to the server, using socket.io, and will be stored in a database. I am using this library to access MySQL from Node.js. Usually, when the user inputs the data, it is fine. But when the data includes single quotations, things aren't working. Here's an example:
let data = "LET'S GO";
// this is the data that the user inputs
// if it has single quotations in it, the code doesn't work
// otherwise it does
connection.getConnection(function(error, conn) {
if (error) throw error; // if there's an error while connecting, throw it
conn.query(
`INSERT INTO table_name (column) VALUES ('${data}')`, // make query with this MySQL call
function(err, result) {
conn.release();
if (err) throw err; // if there's an error with the call, throw it. Usually where my error comes
}
)
})
As commented in the code above, if the data variable has single quotations in it, the MySQL will return an error like the following:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's go.' at line 1
After initially running into this error, I've been looking for a solution. On this page, it says to use mysql.escape(), connection.escape() or pool.escape() to eliminate this issue. I've looked all over Stack Overflow solutions, but they all seem to point back to this. The issue with this solution is that the data is entered into the MySQL query with two single quotations around it. Therefore, the query looks a bit like this:
INSERT INTO table_name (column) VALUES (''DATA_HERE'')
Instead of:
INSERT INTO table_name (column) VALUES ('DATA_HERE')
In addition, I've looked at similar issues with mysql.escape(), connection.escape(), and pool.escape(), but they have been of no help because most of them do not have direct solutions.
Is there any OTHER way to escape these single quotations (apostraphes), if they were to appear in the data that the user inputs?
Thank you in advance. Any and all help is appreciated.
Alright, so it looks like I found an answer. My query has to be formatted in a way similar to prepared statements (thanks to @danblack for this). It should be done as such:
conn.query(
`INSERT INTO table_name (column) VALUES (?)`,
[data],
function(err, result) {
conn.release();
if (err) throw err;
}
)
I replaced '${data}' with ? and, in the next argument of the conn.query() function, I gave the value that ? should be replaced with.
If there is more than one data value that needs to be "escaped" in a similar way, you would do this:
conn.query(
`INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)`,
[data1, data2, data3],
function(err, result) {
conn.release();
if (err) throw err;
}
)
If anyone else happens to have a similar pending question, I hope this answers.
Note: this way of "escaping" doesn't only work with INSERT statements. I believe it can be used for all other queries.