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.


Answer: 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.

