How should I construct LIKE queries using multiple values-CodePudding

I have a question about searching a product (table) just by one or more letters and come up the columns that I want (name, description and type) but I have a problem on using the keyword. I know it should be a string but i can't figure out a way to do it. Right now i have this

@app.route('/product/<keyword>', methods=['GET'])
def product_search(kw):
    logger.info('GET /product/<keyword>')

    logger.debug(f'keyword: {kw}')

    conn = db_connection()
    cur = conn.cursor()

    try:
        cur.execute('SELECT id, type, name, price from product where name like s% or type like s% or description like %s', (kw,))
        rows = cur.fetchall()

        row = rows[0]

        logger.debug('GET /product/{keyword} - parse')
        logger.debug(row)
        content = {'id': int(row[0]), 'type': row[1], 'name': row[2], 'price': row[3], 'description': row[5]}

        response = {'status': StatusCodes['success'], 'results': content}

    except (Exception, psycopg2.DatabaseError) as error:
        logger.error(f'GET /product/{keyword} - error: {error}')
        response = {'status': StatusCodes['internal_error'], 'results': str(error)}

    finally:
        if conn is not None:
            conn.close()

    return flask.jsonify(response)

And I also wanted to know if I could do

cur.execute('SELECT id, type, name, price from product where name like s% or type like s% or description like s%
%s', (kw,))

a way to basically execute the program but doing it for example using the letter c to search a product like coke or something

 'SELECT id, type, name, price from product where name like '%c' or type like '%c' or description like '%c%'

in other words I want it for the name and type to search by the first letter and the description by the middle of the sentence.

CodePudding user response：

The correct way to interpolate values into SQL queries in Python is to use parameter substitution, as you are already doing, for example:

values = ('Alice', 'Bob')
cur.execute("""SELECT name FROM tbl WHERE name = %s OR name = %s""", values)

The values are correctly quoted by the connector to minimise the risk of errors and of SQL injection. Using string formatting techniques to do this is error prone and not recommended.

To handle values for LIKE queries, you need to append or prepend the "%" characters to the values, like this:

values = (kw   '%',)
cur.execute("""SELECT id, type, name, price from product where name like %s""", values)

If you want to use the same value in multiple places, you can use a dictionary of values instead of a tuple:

values = {'kw': kw   '%', 'kw2': '%'   kw   '%'} 
cur.execute("""SELECT id, type, name, price from product where name like %(kw)s or type like %(kw)s or description like %(kw2)s""", values)

Note that it's OK to build the values themselves using string formatting, like this

values = (f'{kw}%',)
cur.execute(sql, values)

but it is not OK to use string formatting to place the values into the query itself:

# Not OK
cur.execute(f"""SELECT * FROM tbl WHERE name = {kw}""")

CodePudding user response：

You should use the same name in function input parameters as in url, it you do so it will be str already:

@app.route('/product/<keyword>', methods=['GET'])
def product_search(keyword):
    print(type(keyword)) # <class 'str'>