Deleting records on a database

Question

I have a logging command which works perfectly, the only issue is I can not finish the off section of the command. I need it to delete both records (guildid, channel) if the guildid matches. This is what I have tried.

if (args[0] === 'off') {
    message.channel.send('Logging turned off!');
    const del = db.prepare('DELETE FROM logging WHERE guildid = ?;');
    del.run({
        guildid: `${message.guild.id}`
    });
    return;

Looking at the photo, when the args off is ran, I need it to delete the guildid contents (495602...) and channel contents (<#5290...) if the guildid contents matches with the guild the command was ran in.

Answer 1

Your current answer is the incorrect way to use prepared statement. If you use the way you pose, you open yourself up to SQL injection because you aren't interpreting the value you want to use in the statement as a value, you use it as part of the overall statement and then run the statement without parameters. That means that I could potentially provide a value that might not do exactly what you think it will.

For example the following won't do anything,

const $rowid = "3 OR rowid = 4";
const deleteStatement = db.prepare("DELETE FROM lorem WHERE rowid = $rowid");
deleteStatement.run({$rowid});
deleteStatement.finalize();

But this will delete elements with rowid 3 or 4:

const $rowid = "3 OR rowid = 4";
const deleteStatement = db.prepare(`DELETE FROM lorem WHERE rowid = ${$rowid}`);
deleteStatement.run();
deleteStatement.finalize();

Instead, take a look at the sqlite3 documentation here.

You need to actually paramaterize your prepared statement like the following:

const sqlite3 = require("sqlite3").verbose();
const db = new sqlite3.Database(":memory:");

db.serialize(function() {
    // Make the table
    db.run("CREATE TABLE lorem (info TEXT)");

    // Create some dummy data
    const insertStatement = db.prepare("INSERT INTO lorem VALUES (?)");
    for (let i = 0; i < 5; i++) {
        insertStatement.run(`My Data ${i}`);
    }
    insertStatement.finalize();

    // Delete some data
    const deleteStatement = db.prepare("DELETE FROM lorem WHERE rowid = $rowid");
    deleteStatement.run({
        $rowid: 3
    });
    deleteStatement.finalize();

    // Print elements
    db.each("SELECT rowid AS id, info FROM lorem", (err, {id, info}) => console.log(`${id}: ${info}`));
});

db.close();

Answer 2

For anyone in the future looking how to do this, this was the answer. EDIT: Can't mark as an answer until 2 days lol

if (args[0] === 'off') {
message.channel.send('Logging turned off!');
db.prepare(`DELETE FROM logging WHERE guildid = '${message.guild.id}'`).run();
return;

Answer 3

Late to the better-sqlite3 party:

const del = db.prepare('DELETE FROM logging WHERE guildid = ?');
del.run(message.guild.id)