psycopg2 complaining about text : and amazonaws in execute statement

Question

rather annoying and not sure how to fix but here is my psycopg2 code. It is running on a lambda function.

conn = psycopg2.connect(
            host='nope',
            database='nope',
            user='nope',
            password='nope'
        )
        cur = conn.cursor()
        url = "https://nope.s3.us-east-2.amazonaws.com/" + str(rawbucketkey)
        cur.execute('''
                    UPDATE contentcreatorcontentfeedposts_contentfeedpost
                    SET picturemediatype = TRUE, mediakey = ''' + str(rawbucketkey) + ''',
                    mediaurl=''' + url + ''',
                    active = TRUE, postsubmit = FALSE
                        WHERE contentcreator = ''' + userid + '''AND
                        id =''' + contentpostid + ''';
                        COMMIT;
                            ''')
        cur.close()

look at this line mediaurl=''' + url + ''', url is mentioned above in the code:

url = "https://nope.s3.us-east-2.amazonaws.com/" + str(rawbucketkey)

I get syntaxError statements where it is pointing to the : and the amazonaws text in this line url = "https://nope.s3.us-east-2.amazonaws.com/" + str(rawbucketkey)

and I have no idea why. This is the type of problem that makes me want to stop coding and live in the mountains =( HELP why is this happening?

here is the exact error message

[ERROR] SyntaxError: syntax error at or near "amazonaws"
LINE 4: ...           mediaurl=https//shofi-mod.s3.us-east-2.amazonaws....
                                                             ^

Traceback (most recent call last):
  File "/var/task/lambdarunner.py", line 154, in lambda_handler
    cur.execute('''

Don't use string concatenation to insert values into the query, use parameter substitution. See https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries — snakecharmerb, Nov 29 '21 at 08:01

score 1 · Accepted Answer · answered Nov 29 '21 at 09:20

It is very bad security to insert parameters into your SQL as a string. You should pass parameters to ensure that there is no SQL Injection.

Your problem can probably be fixed by wrapping your statement in a double-quote character, rather than using 3 x single quotes:

    cur.execute("
                UPDATE contentcreatorcontentfeedposts_contentfeedpost
                SET picturemediatype = TRUE, mediakey = '" + str(rawbucketkey) + "',
                mediaurl='" + url + "',
                active = TRUE, postsubmit = FALSE
                    WHERE contentcreator = '" + userid + "' AND
                    id = '" + contentpostid + "';
                    COMMIT;
                ")

Or, you could use a Python f-string:

    cur.execute(f"
                UPDATE contentcreatorcontentfeedposts_contentfeedpost
                SET picturemediatype = TRUE, mediakey = '{str(rawbucketkey)}',
                mediaurl='{url}',
                active = TRUE, postsubmit = FALSE
                    WHERE contentcreator = '{userid}' AND
                    id ='{contentpostid}';
                    COMMIT;
                ")

However, please research the proper way to pass parameters otherwise your app is likely to be hacked.

psycopg2 complaining about text : and amazonaws in execute statement

1 Answers1