How to fix SQLite3, Discord Bot error?

Question

Python

The bot must add the emoji to the database, on command, but it gives an error.

import discord
from discord.ext import commands

import sqlite3
from config import settings

import random

client = commands.Bot(command_prefix = settings['PREFIX'], intents =  discord.Intents.all())
client.remove_command('help')

connection = sqlite3.connect('server.db')
cursor = connection.cursor()

@client.event
async def on_ready():
    cursor.execute("""CREATE TABLE IF NOT EXISTS emo (
        emoji TEXT,
        id INT
    )""")
    connection.commit()
    print("Bot started.")
    await client.change_presence(status = discord.Status.dnd)

@client.command(aliases = ['addemoji'])
@commands.has_permissions(administrator = True)
async def __addshopа(ctx, emoji: str = None):
    if emoji is None:
        await ctx.send("Укажите эмодзи какой хотите добавить.")
    else:
        cursor.execute("INSERT INTO emo VALUES ({})".format(emoji))
        connection.commit()

        await ctx.reply(embed = discord.Embed(
            description = f'Эмодзи успешно добавлен.'
        ))

Mistake:

Bot started.
Ignoring exception in command __addshopа:
Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\User\Desktop\GTAMine\gtamine.py", line 280, in __addshopа
    cursor.execute("INSERT INTO emo VALUES ({})".format(emoji))
sqlite3.OperationalError: no such column: 

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column:

Answer 1

Look, let's say your emoji is equal to :duck:, then the following request will be executed: Did you have to : Do ​​you understand now? Okay, let's say you insert quotes into the query, then someone adds quotes to the emoji code and can make the script execute this query: After which the emo table will be deleted. You can’t just insert into the request what the user passes. You need to use placeholders:
INSERT INTO emo VALUES (:duck:)
INSERT INTO emo VALUES (':duck:')
INSERT INTO emo VALUES ('');DROP TABLE emo;--')

cursor.execute("INSERT INTO emo VALUES (?)", (emoji,))

In this case, the database itself will take care of using the values ​​correctly in queries, taking into account the syntax.