MySQL connector Python variables not registering

Question

I'm currently using the mysql-connector-python package to execute database actions on Flask. It's been working so well until suddenly the variables don't seem to working correctly anymore. My code is here:

@bp.route('/addcart', methods=('OPTIONS', 'POST'))
def addcart():
        ...
        userID = session.get("user_id")
        reqDict = request.get_json()
        itemCode = str(reqDict['itemCode'])
        itemAmt = reqDict['itemAmt']

        if userID is not None:
            db = get_db()
            cursor = db.cursor()
            query = ('SELECT %s FROM cartdata WHERE id = %s')

            cursor.execute(query, (itemCode, userID))
            currentNum = cursor.fetchone()[0]

            if currentNum is None:
                stmt = ('UPDATE cartdata SET %s = 1 WHERE id = %s')
                cursor.execute(stmt, (itemCode, userID))
            else:
                currentNum = int(currentNum) + int(itemAmt)
                stmt = ('UPDATE cartdata SET %s = %s WHERE id = %s')
                cursor.execute(stmt, (itemCode, currentNum, userID))
            ....

For some reason, I seem to having trouble with the itemCode variable. When I use it properly, like in the execution of 'query' or 'stmt', it doesn't work. Typically I will get an error saying

" 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 ''p1' = 1 WHERE id = 21'".

However, if I do this:

query = ('SELECT ' + itemCode + ' FROM cartdata WHERE id = %s')
...
stmt = ('UPDATE cartdata SET '+ itemCode +' = 1 WHERE id = %s')
...

It works properly as intended.

EDIT: I've checked my backend, and apparently the UPDATE statement does not actually update anything. So now I'm at a complete loss.

I don't understand why the connector suddenly breaks now for variables. I've checked this variables and its types, but they were the expected types. Any insight would be helpful.

My table schema for 'cartdata' looks something like this:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| p1    | int(8)  | YES  |     | NULL    |       |
| p2    | int(8)  | YES  |     | NULL    |       |
| p3    | int(8)  | YES  |     | NULL    |       |
| p4    | int(8)  | YES  |     | NULL    |       |
| p5    | int(8)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

I don't believe the column names in SELECT x,y,z FROM ... can be used as variable parameters; they must be hardcoded. — John Gordon
– John Gordon, Commented Sep 25, 2018 at 20:50
To provide a full answer, it would be nice if you could provide us with the schema of your table. — Léopold Houdin
– Léopold Houdin, Commented Sep 25, 2018 at 20:55
I've included the schema. As you can see, the fields are all supposed to be integers, and most are null by default. — albert
– albert, Commented Sep 25, 2018 at 21:03
@Swift I understand what parameters are, but that shed no light on my problem — albert
– albert, Commented Sep 25, 2018 at 21:12

Léopold Houdin · Accepted Answer · 2018-09-25 21:12:52Z

1

That's because when MySQL connector injects your variables into the SQL statement, it formats them according to their type.

You can actually see it in the error message that you get:

"p1' = 1 WHERE id = 21'"
  ^

So probably, your SQL query looks like this:

SELECT 'p1' FROM cartdata WHERE id = someId

Which is syntactically invalid SQL...

Your second option however seems okay. Btw, it seems weird to adapt the column you want to select depending on the user's input... I'd highly recommend to validate this value with something efficient...

Details

You cannot use %s for column names since this injects a string value in your SQL query and this results in a non valid SQL syntax (column names are not string values).

As above:

SET %s = ...

Generates:

SET 'colName' = ...

which is not valid because you are attempting to affect a value to another value...

That would be the same as trying to do the following in python:

'foo' = 'bar'

or

'foo' = 4

You can use %s when setting values (using SET colName = %s) or filtering values (using WHERE colName = %s) because the type of the values in the column colName is actually a string.

As above:

WHERE colName = %s

Generates:

WHERE colName = 'fooBar'

which is valid because you filter on the values that are equal to the string fooBar.

By the way, you might want to check what

SELECT %s FROM cartdata WHERE id = %s

gives you as a result. That could result problems... Actually MySQL won't tell you anything, but you result will probably be exactly the value of itemCode. (it is valid SQL SELECT 'hello', it just returns 'hello').

edited Sep 25, 2018 at 21:12

answered Sep 25, 2018 at 20:52

Léopold Houdin

1,51513 silver badges18 bronze badges

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

8 Comments

albert Over a year ago

Could you elaborate on why this works elsewhere when I use %s. For example, I have: ('SELECT id FROM userinfo WHERE username=%s'), which works perfectly, and that variable is also a string type

albert Over a year ago

I adapted the column selection thing because I wanted to keep track of how many of a particular item (in this case 'p1') each user had. It would've been much easier if I could've just stored a JSON object like in Mongo, but I'm fairly new to MySQL and don't know any other way of doing this

albert Over a year ago

So my second option doesn't seem to raise any errors, but I've checked my database instance and I realize that it isn't updating 'p1' to 1. p1 is still null for id = 21

Léopold Houdin Over a year ago

hey, I tried adding more details, hope it helps. feel free to ask for more if not clear enough :)

albert Over a year ago

This actually explained a lot, thank you! Is there anyway around this then? Because I don't think the second way I'm doing, where I hardcode the 'p1' instead of using a variable, actually executes either. The value of the p1 column is still NULL after the update.

|

Collectives™ on Stack Overflow

MySQL connector Python variables not registering

1 Answer 1

Details

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Details

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related