Python parameterized SQL adding extra apostrophes

Question

Having an issue with a parameterized psycopg2/flask/postgres query inserting an extra apostrophe and wondering how to stop that. I read every article on here that seemed - based on my issue - to answer my question but nope, didn't see any, so here I am! Thanks for any help you can give!

THE ERROR MESSAGE & DEBUG ERROR

psycopg2.errors.SyntaxError: syntax error at or near ")"
LINE 1: ...R t_name LIKE 'rock' OR t_description LIKE 'rock')) LIMIT 20

DEBUG

root:getItems: q = SELECT id, t_part_no, id_category, id_user_modified, id_parent, d_modified, t_name, t_description, t_addr_pdf, t_addr_image, t_addr_site FROM tbl_items  WHERE ( b_enabled = %(t_Item_Enabled)s ) AND (%(t_Item_Search)s)) LIMIT %(t_Item_NumShow)s
root:getItems: t_Item_Search = t_part_no LIKE 'rock' OR t_name LIKE 'rock' OR t_description LIKE 'rock'

THE RELEVANT PYTHON CODE

            t_Item_Search = request.form['box_Search_String']
            t_Item_Where = ""
            t_Item_Where += "t_part_no LIKE '" + t_Item_Search + "'"
            t_Item_Where += " OR t_name LIKE '" + t_Item_Search + "'"
            t_Item_Where += " OR t_description LIKE '" + t_Item_Search + "'"
            t_Item_Search = t_Item_Where

...

    q += " FROM tbl_items "
    q += " WHERE "
    q += "("
    q += " b_enabled = %(t_Item_Enabled)s"
    if t_Item_Search != '':
        q += " ) AND ("
        q += "%(t_Item_Search)s"
        q += ")"
    q += ")"
    if t_Item_OrderBy != '':
        q += " ORDER BY "
        q += "%(t_Item_OrderBy)s "
        q += "%(t_Item_UpDown)s"
    q += " LIMIT %(t_Item_NumShow)s"
    logging.debug("getItems: q = " + q)
    logging.debug("getItems: t_Item_Search = " + t_Item_Search)
    vars = {
        "t_Item_Enabled": (t_Item_Enabled=='True'),
        "t_Item_Search": AsIs(t_Item_Search),
        "t_Item_OrderBy": t_Item_OrderBy,
        "t_Item_UpDown": t_Item_UpDown,
        "t_Item_NumShow": int(t_Item_NumShow)
        }
    db_cursor.execute(q, vars)

Maurice Meyer · Accepted Answer · 2020-09-07 21:12:02Z

1

Use AsIs to use t_Item_Search as SQL representation and not as a string.
You might want to use multi-line strings (using triple quotes), that makes it easier to write longer/more complex sql statements:

from psycopg2.extensions import AsIs
...
cur = conn.cursor()
values = {
    "t_Item_Enabled": True, 
    "t_Item_Search": AsIs(" AND t_part_no LIKE 'rock' OR t_name LIKE 'rock' OR t_description LIKE 'rock'"),
    "t_Item_OrderBy": "", 
    "t_Item_UpDown": "", 
    "t_Item_NumShow": 20
}

sql = """
    SELECT
        foo,
        bar,
        baz
    FROM
        some_table
    WHERE
        (
        b_enabled = %(t_Item_Enabled)s
        )
        %(t_Item_Search)s
    ORDER BY
        baz
    LIMIT
        %(t_Item_NumShow)s
"""
print(cur.mogrify(sql, values).decode('utf-8'))

Output:

SELECT
    foo,
    bar,
    baz
FROM
    some_table
WHERE
    (
    b_enabled = true
    )
    AND t_part_no LIKE 'rock' OR t_name LIKE 'rock' OR t_description LIKE 'rock'
ORDER BY
    baz
LIMIT
    20

edited Sep 7, 2020 at 21:12

answered Sep 7, 2020 at 19:25

Maurice Meyer

18.2k4 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

ScotterMonkey Over a year ago

Looks great! I have one question about your output. Noticing no " and " between the b_enabled and the rest of the where clause. Is that a typo or it's totally ok to execute sql that way?

ScotterMonkey Over a year ago

Forgive my persistence/curiosity. Are you saying no "and" or comma are needed between parts of a WHERE clause? Like "WHERE ( b_enabled = true ) t_part_no ... " vs "WHERE ( b_enabled = true ) AND t_part_no... " ?

Maurice Meyer Over a year ago

Yes, now i see it. You are right I missed an AND :)

ScotterMonkey Over a year ago

OK then I'm not crazy, hehe. I wasn't sure if it was a Postgres-only mod to SQL, heh.

Maurice Meyer Over a year ago

Great, try not to format the SQL statement on your own, like: t_Item_Where += "t_part_no LIKE '" + t_Item_Search + "'", let psycopg2 do this work for you and debug the SQL statement using mogrify

|

Collectives™ on Stack Overflow

Python parameterized SQL adding extra apostrophes

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related