Python's string formatting throws error when applied within mysql

Question

I've written a script in python to scrape some data from a website and store them in mysql. My script successfully do the job if I opt for the two options to insert the data:

mycursor.execute("INSERT INTO webdata (name,bubble,review) VALUES ('{}','{}','{}')".format(name,bubble,review))
mycursor.execute("INSERT INTO webdata (name,bubble,review) VALUES (%s,%s,%s)",(name,bubble,review))

However, It throws an error when I try to do the same using python's new string formatting like below:

mycursor.execute("INSERT INTO webdata (name,bubble,review) VALUES (f'{name},{bubble},{review}')")

Error It throws:

line 429, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''{name},{bubble},{review}')' at line 1

Where I'm going wrong and how to fix it as I'm very willing to stick to the last formatting style?

use the 2nd option with parametrized query: bobby-tables.com don`t do string interpolation — Patrick Artner
– Patrick Artner, Commented Nov 4, 2018 at 19:35

dlink · Accepted Answer · 2018-11-06 18:13:54Z

Better to let the MySQL Connector bind the variables, by using %s's. This avoids SQL injection. Here a working example.

import MySQLdb

# set up db connection
dbApi = MySQLdb
connection = MySQLdb.connect(
    host    = <hostname>,
    user    = <databasename>,
    passwd  = password,
    db      = <dbname>,
    port    = 3306,
    charset = "utf8")
cursor = connection.cursor(dbApi.cursors.DictCursor)

# insert records
records = [['George', 'Ten', 'Good'],
           ['Ringo', 'Ten', 'Good'],
           ['Paul', 'Ten', 'Good'],
           ['John', 'Ten', 'Good']]
insert_sql = 'insert into webdata (name, bubble, review) values (%s, %s, %s)'

for record in records:
    cursor.execute(insert_sql, record)

# list record
sql = 'select * from webdata'
cursor.execute(sql)
data = cursor.fetchall()
print 'data:', data

connection.commit()
cursor.close()

AKX · Accepted Answer · 2018-11-04 19:27:57Z

1

You can't use f-strings with databases, simple as that, if you want to write code not vulnerable to SQL injection vulnerabilities.

answered Nov 4, 2018 at 19:27

AKX

171k16 gold badges146 silver badges228 bronze badges

Collectives™ on Stack Overflow

Python's string formatting throws error when applied within mysql

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