[FIXED] Python parameterized SQL adding extra apostrophes

Issue

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)

Solution

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

Answered By - Maurice Meyer