0

I am getting the following error: 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 %s How do I add a variable to my sql query?

#!/usr/bin/python

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="",
    password="",
    database="seahub_db"
)

mycursor = mydb.cursor()
ip = "Myip"
sql = "UPDATE constance_config SET value = %s WHERE id=1"
mycursor.execute(sql, ip)

mydb.commit()
mycursor.close()
mydb.close()
Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
Oleg Kolosovsky
  • 1
  • 7
  • Please read about SQL Injections and see if you feel your code is secure. – dfundako Feb 02 '21 at 15:42
  • @dfundako Where do you see a possibility for injection? This is a parameterized query, no string interpolation in Python is happening. – Thomas Feb 02 '21 at 15:43
  • Does this answer your question? [Use of '.format()' vs. '%s' in cursor.execute() for mysql JSON field, with Python mysql.connector,](https://stackoverflow.com/questions/48653106/use-of-format-vs-s-in-cursor-execute-for-mysql-json-field-with-pytho) – Tomerikoo Feb 02 '21 at 15:43

2 Answers2

1

The cursor's execute() function expects a tuple as the second parameter:

mycursor = mydb.cursor(prepared=True)
ip = ("Myip",)
sql = "UPDATE constance_config SET value = %s WHERE id=1"
mycursor.execute(sql, ip)

Note also that I open the cursor in prepared statement mode. You might not need to do this explicitly, but I am doing it for pedagogical reasons.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • How does that explain the SQL syntax error though? – Thomas Feb 02 '21 at 15:43
  • 1
    @Thomas If Python passes a malformed prepared statement, what would make you think that it might not result in a MySQL syntax error on the database side? – Tim Biegeleisen Feb 02 '21 at 15:44
  • Of course, but I don't see a malformed prepared statement here. The `UPDATE` statement seems valid to me, yet MySQL raises a _syntax error_, not something like "expected tuple but got string". Or am I missing something? – Thomas Feb 02 '21 at 15:49
  • We don't know what Python ends up passing to MySQL if we don't use a tuple to store the parameters. – Tim Biegeleisen Feb 02 '21 at 15:50
-1
ip = "Myip"
sql = "UPDATE constance_config SET value = '" + ip + "' WHERE id=1"

It is important not to forget the quotes in the "sql" character string so that your variable is indeed a string in your sql request

ZaRToP
  • 31
  • 5
  • Your answer is _wide open_ for SQL injection and should never be used in production. Read about how to use prepared statements to avoid this pattern. – Tim Biegeleisen Feb 02 '21 at 15:48