use row as variable with python and sql

Question

I am trying to update some values into a database. The user can give the row that should be changed. The input from the user, however is a string. When I try to parse this into the MySQL connector with python it gives an error because of the apostrophes. The code I have so far is:

import mysql.connector
conn = mysql.connector
conn = connector.connect(user=dbUser, password=dbPasswd, host=dbHost, database=dbName)
 cursor = conn.cursor()
cursor.execute("""UPDATE Search SET %s = %s WHERE searchID = %s""", ('maxPrice', 300, 10,))

I get this error

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''maxPrice' = 300 WHERE searchID = 10' at line 1

How do I get rid of the apostrophes? Because I think they are causing problems.

• khelwood: A column name is not a parameter. Put the column name maxPrice directly into your query.
• Joost Luijben: I want to make the column a parameter. Is this possible? Or is there a workaround?

Answer 1

As noted, you can't prepare it using a field.

Perhaps the safest way is to allow only those fields that are expected, e.g.

#!/usr/bin/python
import os
import mysql.connector
conn = mysql.connector.connect(user=os.environ.get('USER'),
 host='localhost',
 database='sandbox',
 unix_socket='/var/run/mysqld/mysqld.sock')
cur = conn.cursor(dictionary=True)
query = """SELECT column_name
 FROM information_schema.columns
 WHERE table_schema = DATABASE()
 AND table_name = 'Search'
 """
cur.execute(query)
fields = [x['column_name'] for x in cur.fetchall()]
user_input = ['maxPrice', 300, 10]
if user_input[0] in fields:
 cur.execute("""UPDATE Search SET {0} = {1} WHERE id = {1}""".format(user_input[0], '%s'),
 tuple(user_input[1:]))
print cur.statement

Prints:

UPDATE Search SET maxPrice = 300 WHERE id = 10

Where:

mysql> show create table Search\G
*************************** 1. row ***************************
Search
CREATE TABLE `Search` (
 `id` int(11) DEFAULT NULL,
 `maxPrice` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 2

A column name is not a parameter. Put the column name maxPrice directly into your SQL.

cursor.execute("""UPDATE Search SET maxPrice = %s WHERE searchID = %s""", (300, 10))

If you want to use the same code with different column names, you would have to modify the string itself.

sql = "UPDATE Search SET {} = %s WHERE searchID = %s".format('maxPrice')
cursor.execute(sql, (300,10))

But bear in mind that this is not safe from injection the way parameters are, so make sure your column name is not a user-input string or anything like that.

Answer 3

You cannot do it like that. You need to place the column name in the string before you call cursor.execute. Column names cannot be used when transforming variables in cursor.execute.

Something like this would work:

sql = "UPDATE Search SET {} = %s WHERE searchID = %s".format('maxPrice')
cursor.execute(sql, (300, 10,))

Answer 4

You cannot dynamically bind object (e.g., column) names, only values. If that's the logic you're trying to achieve, you'd have to resort to string manipulation/formatting (with all the risks of SQL-injection attacks that come with it). E.g.:

sql = """UPDATE Search SET {} = %s WHERE searchID = %s""".format('maxPrice')
cursor.execute(sql, (300, 10,))

• Joost Luijben: Yes, this works! I am indeed worried about the SQL-injection, but I guess I have no other choice...
• cEz: @JoostLuijben see my answer to allow only valid fields stackoverflow.com/a/49039356/263671