issue while creating an endpoint in Flask using Python

Question

I'm trying to write a microservice for a RESTful web service. I've a database created in 'Postgresql' and currently using Flask and psycopg2 (for conversion of db-object to json object). Following is a part of my code, but for some reason I'm getting an error. The URI i'm trying to establish is somewhat like this localhost/events/20171222 What should be right way to handle this?

Code:

app = Flask(__name__)
conn = psycopg2.connect("dbname='postgresdb'")
cur = conn.cursor(cursor_factory=RealDictCursor)

@app.route('/events/<dated>', methods=['GET'])
def getDatedEvents(dated):
    date_obj = datetime.strptime(dated, '%Y%m%d')
    #print(type(date_obj))
    #print(date_obj)

    cur.execute("""
        SELECT event_id, timestamp
        FROM event_tbl
        WHERE timestamp < date_obj
        ORDER BY timestamp
        LIMIT 25
        """)

    return json.dumps(cur.fetchall(), default=json_serial)

Error Output:

psycopg2.ProgrammingError: column "date_obj" does not exist
LINE 4:   WHERE timestamp < date_obj
                    ^

localhost - - [22/Dec/2017 17:22:29] "GET /events/20161020 HTTP/1.1" 500 -

py_dude · Accepted Answer · 2017-12-23T09:04:28.517

-1

You need to modify your query. Currently you are comparing timestamp with string date_obj which is why postgreSQL is throwing you error, since it cannot compare timestamp with string. Use string formatting to pass your date_obj in query:

cur.execute("""
    SELECT event_id, timestamp
    FROM event_tbl
    WHERE timestamp < %s
    ORDER BY timestamp
    LIMIT 25
    """, (date_obj,))`

According to docs, as mentioned in the comments below, the previous revision of an answer could cause SQL injections, so be aware of using string formatting, and use the API correctly.

edited Dec 23 '17 at 09:04

answered Dec 22 '17 at 12:07

py_dude

822
5
13

Oh thanks buddy :) @py_dude – Aman Singh Dec 22 '17 at 12:11
@GabbarSingh you can mark this answer as correct if it helped you :) – py_dude Dec 22 '17 at 12:12
3

Don't `format` the query, that's how you create a SQL Injection vulnerability in your code. Use query parameters: https://stackoverflow.com/questions/1466741/parameterized-queries-with-psycopg2-python-db-api-and-postgresql – leovp Dec 22 '17 at 14:40

issue while creating an endpoint in Flask using Python

1 Answers1