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.

A column name is not a parameter. Put the column name maxPrice directly into your query. — khelwood
– khelwood, Commented Feb 28, 2018 at 21:35
I want to make the column a parameter. Is this possible? Or is there a workaround? — Joost Luijben
– Joost Luijben, Commented Feb 28, 2018 at 21:36

cEz · Accepted Answer · 2018-02-28 22:03:33Z

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

khelwood · Accepted Answer · 2018-02-28 21:38:07Z

1

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.

answered Feb 28, 2018 at 21:38

khelwood

59.7k14 gold badges91 silver badges116 bronze badges

Comments

Tim Shaffer · Accepted Answer · 2018-02-28 21:38:13Z

1

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,))

answered Feb 28, 2018 at 21:38

Tim Shaffer

1662 silver badges8 bronze badges

Comments

Mureinik · Accepted Answer · 2018-02-28 21:38:48Z

1

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,))

answered Feb 28, 2018 at 21:38

Mureinik

316k54 gold badges399 silver badges405 bronze badges

2 Comments

Joost Luijben Over a year ago

Yes, this works! I am indeed worried about the SQL-injection, but I guess I have no other choice...

cEz Over a year ago

@JoostLuijben see my answer to allow only valid fields stackoverflow.com/a/49039356/263671

Collectives™ on Stack Overflow

use row as variable with python and sql

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related