0

Working on a simple product manager and came across a mysql error that for the life of me I can't fix. So the error is, when "new" is in the title of something inserted into PRODUCT_ID, an error like this

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''PRODUCT_ID', price, PRODUCT_URL, image_location ) VALUES ( `...' at line 1")

occurs. Adding quotes did not help, rather confusing.

    price = price
    PRODUCT_URL = url
    image_location = ""
    
    query = f"INSERT INTO PRODUCT ( 'PRODUCT_ID', price, PRODUCT_URL, image_location ) VALUES ( `{PRODUCT_ID}`, '{price}', '{PRODUCT_URL}', '{image_location}')"
    
    cur.execute(query)
    print(f"{cur.rowcount} details inserted")
    conn.commit()
    conn.close()
Barmar
  • 741,623
  • 53
  • 500
  • 612
developer_dude
  • 13
  • 3
  • 1
    Don't use string substitution. Use placeholders with parameters in `cursor.execute()`. – Barmar May 16 '22 at 17:04
  • Not confusing - that's a classic SQL injection vulnerability. No amount of quoting is going to fix this. Imagine what would happen if one of those strings contained `'); DROP TABLE PRODUCT; #`. Use parameters instead – Panagiotis Kanavos May 16 '22 at 17:04
  • Also, when you put something in backticks it should be a column name, not a string. See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Barmar May 16 '22 at 17:06
  • Even if you ensured no variables contained eg single quotes, you'd still run into trouble due to the localized conversion of numbers and dates to strings. When you use parameters though, the parameter value is passed as a binary value outside the query directly to the server. This way you avoid conversion errors *and* use less bandwidth – Panagiotis Kanavos May 16 '22 at 17:06
  • Are you sure that's the code that generated this error message? It should say the error is near something in the `VALUES` list, not the list of columns. – Barmar May 16 '22 at 17:07
  • would that just be a string of the same format in cursor.execute? – developer_dude May 16 '22 at 17:08
  • @Barmar product_id is in single quotes in the field list triggering the error message in the question. – Shadow May 16 '22 at 20:20
  • Right. That should be in backticks, not single quotes. – Barmar May 16 '22 at 20:22

0 Answers0