Python/MySQL query error: `Unknown column`

Question

This script is meant to act as a command-line front-end to add records to a locally hosted MySQL database.

I am getting this error:

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'watermelon' in 'field list'

But watermelon is the value I am trying to enter, not the column name!

Here is the script:

#! /usr/bin/python

#use command line as front end to enter new rows into locally hosted mysql database

import mysql.connector

#create inputs
new_fruit = raw_input('What fruit do you want to add? ')
new_fruit_type = raw_input('Which type of ' + new_fruit + '? ')

#connect to dbase
conn = mysql.connector.connect(user='root', password='xxxx', database='play')

#instansiate cursor
cursor = conn.cursor()

#define sql statement
add_record = "INSERT INTO fruit (name, variety) VALUES (%s, %s)" % (new_fruit, new_fruit_type)

#execute sql
cursor.execute(add_record)

#close out
conn.commit()
cursor.close()
conn.close()

And the table schema:

mysql> describe fruit;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| name    | char(30) | YES  |     | NULL    |                |
| variety | char(30) | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Answer 1

"INSERT INTO fruit (name, variety) VALUES (%s, %s)" % ("watermelon", "melon")

Literally becomes

INSERT INTO fruit (name, variety) VALUES (watermelon, melon)

Instead of strings, watermelon and melon are columns. To fix this, put quotes around your %s.

"INSERT INTO fruit (name, variety) VALUES ('%s', '%s')" % (new_fruit, new_fruit_type)

However, you should run it as:

cursor.execute("INSERT INTO fruit (name, variety) VALUES (%s, %s)", (new_fruit, new_fruit_type));

Notice we took away the quotations around the %s and are passing the variables as the second argument to the execute method. Execute prevents sql injection from the variables as well as wraps strings in quotation marks.

For more information, see http://mysql-python.sourceforge.net/MySQLdb.html#some-examples

Answer 2

The issue is here:

add_record = "INSERT INTO fruit (name, variety) VALUES (%s, %s)" % (new_fruit, new_fruit_type)

Imagine the query this would produce:

INSERT INTO fruit (name, variety) VALUES (watermelon, something_else)

Those values aren't values anymore! They look more like column references (Unknown column 'watermelon' in 'field list')

Instead, you should use prepared statements:

query = "INSERT INTO fruit (name, variety) VALUES (%s, %s)"
cursor.execute(query, (new_fruit, new_fruit_type))

This will automatically take care of the parameterization for you, and will prevent SQL Injection