0

I have an issue when I am trying to retrieve data from a postgres database using psycopg2 cursor. I am using a string.format to pass my variable name in the cursor query, but whenever the name contains a single quote, the quote won't be escaped and an error will be raised.

Here is my python code :

def getFirstOrderDate(exact_hotel_name):

    ## Retrieve the first order date to know how old is the property ##

    con=psycopg2.connect(dbname=dbname(),host=host(),port=port(),user=user(),password=password())
    cur=con.cursor()
    firstOrderTxt="Select hotel_name,exact_hotel_name,min(order_date) from fact.podio_orders where exact_hotel_name = '{}' group by hotel_name,exact_hotel_name".format(str(exact_hotel_name))
    cur.execute(firstOrderTxt)
    firstOrder=cur.fetchall()
    cur.close()
    con.close()
    return firstOrder[0][2]

And it raised this error, which is obviously logic, but I don't succeed to find a solution: enter image description here

I tried to modify firstOrderTxt by this firstOrderTxt='Select hotel_name,exact_hotel_name,min(order_date) from fact.podio_orders where exact_hotel_name = "{}" group by hotel_name,exact_hotel_name'.format(exact_hotel_name) but I am getting this error, where I don't understand why my variable name is looked for as a column of the table: enter image description here

Can you help me on this?

Thanks

Barthelemy Lancelot
  • 41
  • 6
  • https://stackoverflow.com/a/45128973/235698. Using `.format` allows SQL injection attacks. Use a parameterized query. – Mark Tolonen Jul 15 '19 at 04:47

1 Answers1

1

You shouldn't use .format in SQL statements unless you REALLY have a good reason to. ESPECIALLY with user input!. They are very easy to SQLI (unless you really want to go through every single escape/check/replace individually). Below is an example of what you are doing, vs what you should do rather than using .format:

Incorrect:

cursor.execute("SELECT * FROM x WHERE y = {}".format("customer"))

Correct:

cursor.execute("SELECT * FROM x WHERE y = %s", ["customer"])
cmyui
  • 81
  • 4