How to prevent SQL injection attacks in discord bot python

Question

For my discord bot I have use sqlite as my database. Many people said that using sqlite is not good because you may get SQL injected. They have recommended me to use aiosql.

I know how sql inject work on a website but I don't know how this could be done in discord bot. I want to know a way in which these kind of SQL attack work on a discord bot. I also want to know a way to prevent this attack from happening in the future.

This is the example of my code.

@client.command()
@commands.has_permissions(administrator=True)
async def setwelcome(ctx , channel:discord.TextChannel):
    db = sqlite3.connect('Smilewin.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT welcome_id FROM Main Where guild_id = {ctx.guild.id}")
    result = cursor.fetchone()
    if result is None:
        sql = ("INSERT INTO Main(guild_id, welcome_id) VALUES(?,?)")
        val = (ctx.guild.id , channel.id)

        embed = discord.Embed(
            colour= 0x00FFFF,
            title = "ตั้งค่าห้องเเจ้งเตือนคนเข้าเซิฟเวอร์",
            description= f"ห้องได้ถูกตั้งเป็น {channel.mention}"
        )

        message = await ctx.send(embed=embed)
        await message.add_reaction('✅')

    elif result is not None:
        sql = ("UPDATE Main SET welcome_id = ? WHERE guild_id = ?")
        val = (channel.id , ctx.guild.id)
        
        embed = discord.Embed(
            colour= 0x00FFFF,
            title= "ตั้งค่าห้องเเจ้งเตือนคนเข้าเซิฟเวอร์",
            description= f"ห้องได้ถูกอัพเดตเป็น {channel.mention}"
        )
        
        message = await ctx.send(embed=embed)
        await message.add_reaction('✅')

    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()

The full code will be in this link below: https://github.com/reactxsw/Smilewinbot/blob/main/SmileWinbot.py

You don't use f-strings to construct SQL. You use the way explained in the docs. — Klaus D.

NotARealPerson NotARealPerson · Accepted Answer · 2021-01-27T17:45:52

cursor.execute(f"SELECT welcome_id FROM Main Where guild_id = {ctx.guild.id}")

this line is pretty much the only source of concern,

It would probably be ok but it is still bad practice to ever use an f- string when constructing an SQL statement.

other-wise all the other queries appear to be sanitized correctly and you are not vulnerable to an SQL injection

How to prevent SQL injection attacks in discord bot python

1 Answers