MySQL update with variable

Question

I am encountering a problem while updating a table. The code is from telegram bot. We are receiving messages from user, and asking him for a name. This is the variable 'first_name'. We already know his user_id which is integer. Then I am trying to do

def bd_set(body):
    cursor.execute(body)
    connect.commit()

bd_set(f"INSERT INTO user_info (user_id,first_name) VALUES({user_id},{first_name})")

and getting an error: no such column "John".

But if I try without variable, the code works:

bd_set(f"INSERT INTO user_info (user_id,first_name) VALUES({user_id},'John')")

So, I cannot input varibale (first_name), while variable'user_id' inputs easily.

what can solve the issue?

Answer 1

The code in the question and in both existing answers has the same major problem: they are wide open to a serious security vulnerability called SQL injection.

Don't build SQL queries by sticking strings together.

Instead, pass the parameters to cursor.execute() separately:

def bd_set(body, parameters=None):
    cursor.execute(body, parameters)
    connect.commit()

bd_set(
    f"INSERT INTO user_info (user_id, first_name) VALUES(%s, %s)",
    (user_id, first_name),
)

I replaced the placeholders here with %s but depending on your database driver you might have to use different syntax.