I have my query like so:
let thequery = "insert into USERS (USERNAME) VALUES ('" + username + "')"
but when I insert the query into my database (I am storing the query for analytical purposes), it fails because of the quotes.
var insertAnalyticsLogin = "insert into ANALYTICS (username, location, device, query, timeoflogin) VALUES ('" + username + "', '" + location + "', '" + device + "', '" + thequery + "', '" + timeoflogin + "')"
how can I fix this? I tried converting thequery to toString(), but that was a useless attempt. Any other ideas?
edit:
error i am recieving is:
sqlMessage: "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 'email@email.com')', '1/5/2022, 11:32:54 AM')' at line 1",
sqlState: '42000',
index: 0,
sql: "insert into ANALYTICS (username, location, device, query, timeoflogin) VALUES ('email@email.com', 'n/a', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.2 Safari/605.1.15', 'insert into USERS (USERNAME) VALUES ('email@email.com')', '1/5/2022, 11:32:54 AM')"
This is a great example of how using parameterized queries is better than using string-concatenation to format SQL queries. You can use parameter placeholders and then you never have to worry about the possible literal quote characters in your string variables.
var insertAnalyticsLogin = "insert into ANALYTICS (username, location, device,
query, timeoflogin) VALUES (?, ?, ?, ?, ?)"
mysqlconn.query(insertAnalyticsLogin,
[username, location, device, thequery, timeoflogin],
function(err, rows) {
...
You don't even need the single-quotes around the ? placeholders. In fact, you must not quote the placeholders, or else it will try to insert a literal string "?" instead of treating it as a parameter.
Using query parameters makes your code easier to read and easier to write, without getting eyestrain trying to balance all those quotes-within-quotes.