How to Mysql escape in PYTHON

Question

I have some data stored in an array, among these there is a message field that can contain different kind of characters.. Among them the quotes characters.

    import MySQLdb
    for key, value in result_array.items(): 
            insert_array.append("`%s` = \"%s\"" %(key, value))
    insert_values = " , ".join(insert_array)
    query_insert = "INSERT into `%s` SET %s ON DUPLICATE KEY UPDATE %s" %(insert_table, insert_values, insert_values)
    cursor.execute(query_insert)

This code fails if the message contains a double quote character. How to escape it?

P.S.

I want to focus on the difference between connection.escape_string and cursor.execute(operation, params=None, multi=True) with params. There was an asnwer (now deleted) that was suggesting to use connection.escape_string and as some people who replied said that is not safe. This is because escape query manually could be dangerous and it is better use parameterise function. [someone might be better than me in explaining this concept, please welcome to edit this part]

Which is the python module that you are using to connect to MySQL? — Walter_Ritzel
– Walter_Ritzel, Commented May 9, 2016 at 16:16
Why the heck are you using a formatted string for SQL? You should almost never do this. It invites SQL injection. — jpmc26
– jpmc26, Commented May 9, 2016 at 18:14
@jpmc26 well, in this particular case is not a web app or some other things that users can use. It's a script that run in batch. But, please, enlight me, what should I use it? — salvob
– salvob, Commented May 9, 2016 at 22:42
@salvob Parameterized queries. Every language I know of has some form of them. glglgl's answer is using one (second parameter to execute). These escape your input by default to prevent SQL injection. It's harder to do this when you need to vary the actual table or column names, but you should think carefully about if there's some way to avoid doing that and just hard code the names instead. At a bare minimum, I would provide some kind of whitelisting on the names coming into my function. If for no other reason, then because I don't want some other dev to grab my code thinking it's safe. — jpmc26
– jpmc26, Commented May 9, 2016 at 22:45

glglgl · Accepted Answer · 2016-05-09 16:19:10Z

4

By using the execute() call properly:

data = []
for key, value in result_array.items():
    # only proper if key is NOT user defined!
    insert_array.append("%s = %%s" % key) # results in key = %s
    data.append(value)
insert_values = " , ".join(insert_array)
query_insert = "INSERT into `%s` SET %s ON DUPLICATE KEY UPDATE %s" %(insert_table, insert_values, insert_values)
cursor.execute(query_insert, data * 2)

Didn't test it, and hopfilly understood your code right.

The point is to pass the real data to the execute() call which is supposed to escape it for you. It does so by means of the database functions and thus does it better than you ever could.

answered May 9, 2016 at 16:19

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

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

5 Comments

salvob Over a year ago

Thank you for your reply. but I have a few question: The query_insert has three %s, while in your code of execute you put as second parameter of the function, data *2 . How can Python figured out where to substitute those paraameters? Did you forget the value variable on the loop? or it was on purpose? if so, why?

tadman Over a year ago

This does look like the right way to do it, but it could use some explanation. There's a lot going on in there.

salvob Over a year ago

Aww, I see what you did, you put in insert_array a nested/smart interpolation, then you put in the array data just the values, in this way execute can understand which is the data needed. great! thank you

glglgl Over a year ago

The query_insert gets its %s replaced by insert_table and twice insert_values, as in your case. At the end, it looks like INSERT INTO table SET foo=%s, bar=%s ON DUPLICATE KEY UPDATE foo=%s, bar=%s. data is [foo_value, bar_value], so the execute() call gets [foo_value, bar_value, foo_value, bar_value] which perfectly fits the four %ss in the query.

Eric Platon Over a year ago

A solution based on escape_string() is way less painful, especially with even more involved queries such as bulk inserts.

Collectives™ on Stack Overflow

How to Mysql escape in PYTHON

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related