Correct python code to insert NULL value into MySQL

Question

I'm attempting to insert some variables into a MySQL database using python. Sometimes the variables will be strings and sometimes they will be None.

A hugely simplified version of what I am attempting is below:

import MySQLdb as mdb
f = "Bob"
m = None
l = "Smith"

db = mdb.connect("blah blah blah")
with db:
    cur.execute("INSERT INTO name (first, middle, last) VALUES ('{0}', '{1}', '{2}')".format(f, m, l))

The problem is that it inserts 'None' as a string rather than as an actual NULL value.

I can solve the problem by changing the last line to:

cur.execute("INSERT INTO name (first, middle, last) VALUES (%s, %s, %s)", (f, m, l))

but I believe that this leaves the code vulnerable to MySQL injection attacks. If anyone knows the correct way using the .format method I would be very grateful.

No, the string formatting leaves you vulnerable. Passing values the second way is what is expected. — That1Guy
– That1Guy, Commented Jun 25, 2015 at 15:12
Thanks - I'm glad I got my apology for the dumb question in early — Ken Williams
– Ken Williams, Commented Jun 25, 2015 at 15:13
Its not a dumb question. Its important you know how to protect against SQL injection. — That1Guy
– That1Guy, Commented Jun 25, 2015 at 15:14

That1Guy · Accepted Answer · 2015-06-25 15:15:52Z

1

The expected method of inserting data is the following:

cur.execute("INSERT INTO name (first, middle, last) VALUES (%s, %s, %s)", (f, m, l))

Formatting a string like in your first example leaves your application vulnerable to SQL injection.

answered Jun 25, 2015 at 15:15

That1Guy

7,2335 gold badges50 silver badges60 bronze badges

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

1 Comment

Neurotransmitter Over a year ago

Exactly. Neither deprecated %, nor .format will not work in this case.

Collectives™ on Stack Overflow

Correct python code to insert NULL value into MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related