Issue
I'm writing telegram bot in python with sqlite as database. I use aiosqlite as framework for connecting to database and executing values. In database I have 3 columns and it looks like:
user_id, user_name, balance
802123124,@toppythonguy,0
79124142124,@None,0
And when I get balance using user_id by this function it works fine
async def tryer(userid, column="user_id"):
sql_connection = await aiosqlite.connect("users.db")
sql_cursor = await sql_connection.cursor()
await sql_cursor.execute(f'''SELECT balance FROM users WHERE {column} == {userid}''')
user_balance = await sql_cursor.fetchall()
await sql_connection.close()
if not user_balance:
return False
else:
return user_balance
But when I'm trying same code with username (like: @Goodfella) I'm getting error:
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.
I've tried some solutions from stackowerflow but none of them works form me
Solution
The problem is that you’re using an f-string to construct your SQL statement. When you try to use user_name
you end up with
SELECT BALANCE FROM users WHERE user_name = @toppythonguy
SQLite uses @
(along with a few other characters) to indicate a bound parameter, in this case one named toppythonguy
. It’s expecting you to provide the value when you call execute
.
Wrapping the value in quotes should help. It would probably break your user_id
query, though, since you’d be using a string instead of an integer.
The real fix is to always use a bound parameter.
await sql_cursor.execute(f”SELECT balance FROM users WHERE {column} = ?”, userid)
As a general rule of thumb you don’t want allow user provided values into a SQL query that you construct. Using your original query, imagine if someone submitted this as their username
1; DELETE FROM users
Bound parameters are the way to avoid that. Depending on how column
is populated you may need to do something there, too, but as long as the code that calls tryer
selects it from a predetermined list you should be fine.
Answered By - dirn
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.