Search code examples
pythonpostgresqlherokupsycopg2

psycopg2.errors.SyntaxError: syntax error at or near "<"

I have a small discord bot running in Python. I’m trying to implement a trivia question system in, using OpenTDB, but currently, when I try to import the questions into an SQL server, this error pop up:

2020-07-29T03:37:42.477745+00:00 app[worker.1]:     await coro(*args, **kwargs)
2020-07-29T03:37:42.477745+00:00 app[worker.1]:   File "Dmemer.py", line 81, in on_ready
2020-07-29T03:37:42.477746+00:00 app[worker.1]:     download_questions()
2020-07-29T03:37:42.477750+00:00 app[worker.1]:   File "Dmemer.py", line 292, in download_questions
2020-07-29T03:37:42.477753+00:00 app[worker.1]:     cur.execute(f"INSERT INTO trivia (id, category, difficulty, question, correct, wrong1, wrong2, wrong3) VALUES ({id}, {category}, {difficulty}, {question}, {correctans}, {badans1}, {badans2}, {badans3})")
2020-07-29T03:37:42.477754+00:00 app[worker.1]: psycopg2.errors.SyntaxError: syntax error at or near "<"
2020-07-29T03:37:42.477754+00:00 app[worker.1]: LINE 1: ...uestion, correct, wrong1, wrong2, wrong3) VALUES (<built-in ...
2020-07-29T03:37:42.477754+00:00 app[worker.1]:                                                              ^

Here’s the source code on GitHub: https://github.com/ducanh2002123/Dank-Memer-Private-Clone The line of code that’s reporting as broken is in line 292 Thanks in advance

Solution

  • There are at least two issues here:

    1. Using f-strings to set query values: this will not correctly quote all variables (for example, '2020-07-30' might be interpreted as an arithmetical expression), and it may open the application to SQL injection attacks. Use the substitution features provided by the connection:

       cur.execute("""INSERT INTO mytable (col1, col2) VALUES (%s, %s);""", (var1, var2))

    2. You are passing the built-in id function as the first value: the f-string converts it to the string '<built-in function id>'. If you must pass an id value, pass the type that the database expects (usually an int, sometimes a str or UUID). Id values are often automatically generated by the database and so don't need to be passed at all. You might want to check how the id column in your table is configured.