Question 271179 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql query consist of query and values in one variable to execute in python

I am looking for how to write a query which contains query with values as one single parameter execute(sql) unlike query and values passed as separate parameters to execute(query, values).

Below are the details I am looking into.

###Required Solution###

value1 = 'ABC'
value2 = 'XYZ'
query = f'SELECT column1, column2 from table1 where column1 = {value1} or column2 = {value1}'

mycursor = mydb.cursor()
mycursor.execute(sql)
mydb.commit()

Result of above

query output = ‘SELECT column1, column2 from table1 where column1 = ABC or column2 = XYZ’

Error :: Query syntax is invalid

Findings :: I am thinking as value1 and value2 are not getting assigned as string values the issue is causing.

###Working Solution###

sql = "SELECT column1, column2 from table1 where column1 = {value1} or column2 = {value1}"
val = ("ABC", "XYZ")

mycursor = mydb.cursor()
mycursor.execute(sql, val)
mydb.commit()

But I am looking in particular to the details provided in the required solution.

Tags:

2

Answers


  1. 0

    Your required solution is susceptible to SQL injection.
    Always recommend to use parameterized queries instead of directly interpolating or concatenating values into the SQL string.

    However, if you absolutely need to use that approach, ensure that your string values are enclosed in quotes to be treated as strings in the SQL query:

    value1 = "'ABC'"
value2 = "'XYZ'"
query = f'SELECT column1, column2 from table1 where column1 = {value1} or column2 = {value2}'

mycursor = mydb.cursor()
mycursor.execute(query)
mydb.commit()
    Login or Signup to reply.
  2. 0

    As mentioned by Yuri R; f-string generally is prone to SQL injection attacks. Another way of achieving your result is to use %s param if you’re looking to use it.

    Something like:

    value1 = 'ABC'
value2 = 'XYZ'
query = 'SELECT column1, column2 from table1 where column1 = %s or column2 = %s'

mycursor = mydb.cursor()
mycursor.execute(query, (value1, value2))
mydb.commit()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search