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?

OP, I strongly urge you to use the parameterized version of the query I show in my answer instead of the one you have accepted. SQL injection is very serious. — Chris
– Chris, Commented Feb 28, 2022 at 12:46

Chris · Accepted Answer · 2022-02-27 20:23:17Z

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.

answered Feb 27, 2022 at 20:23

Chris

139k139 gold badges316 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nacho · Accepted Answer · 2022-02-27 19:15:24Z

0

You have a problem with quotation marks. As first_name is a string, and as you are using f string, you need to use quotation marks

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

You should try to use prepared statements to avoid this kind of problems and to avoid sql injection

answered Feb 27, 2022 at 19:15

nacho

5,3772 gold badges28 silver badges35 bronze badges

Collectives™ on Stack Overflow

MySQL update with variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related