I’m facing challenges with my chat bot that interacts with MySQL while trying to save user information to my database.
Here’s my code snippet:
client.on('messageCreate', async (msg) => {
if(msg.author.bot) return;
if(msg.channel.type === 'DM') return;
db.query(`SELECT * FROM users WHERE user_id = '${msg.author.id}'`, (error, results) => {
if(error) throw error;
let insertQuery;
if(results.length === 0) {
insertQuery = (`INSERT INTO users (user_id, display_name) VALUES (${msg.author.id}, ${msg.author.username})`);
}
db.query(insertQuery, console.log);
if (error) throw error;
console.log("User added to database");
});
});
I’m trying to capture the user’s ID and name when they message. However, I’m encountering syntax errors with my queries, and I can’t figure out the problem. I’ve experimented with various INSERT formats but nothing seems to work with MySQL 8.0. Any insights would be appreciated!
You’re missing quotes around your string values in the INSERT statement. When inserting msg.author.username, wrap it in quotes since it’s a string. Change your insertQuery to: INSERT INTO users (user_id, display_name) VALUES ('${msg.author.id}', '${msg.author.username}'). See the single quotes around both values? You’ve also got a logic error - you’re checking for error outside the callback scope where that variable doesn’t exist. Move the error handling inside the second db.query callback. Better yet, use parameterized queries instead of string concatenation: db.query('INSERT INTO users (user_id, display_name) VALUES (?, ?)', [msg.author.id, msg.author.username], callback). This prevents SQL injection and handles quoting automatically.
Your code has another bug nobody mentioned yet. You define insertQuery inside the if(results.length === 0) block, but then try to execute it outside that condition. If the user already exists, insertQuery is undefined and your second db.query call crashes. Move that second query inside the if statement or add an else clause for existing users. Also, switch to async/await with mysql2/promise instead of nested callbacks - way cleaner error handling and no callback hell as your bot gets bigger.
your username field prob has special chars that break the sql syntax. usernames with spaces or apostrophes mess up your query even when quoted. use prepared statements like lucas said - they’ll handle char escaping and protect against sql injection.