How can i use a string in mysql query in python? [duplicate]

Question

In the code below, i have a string which represent the mac number. I want to use it in my query. Normally it will change dynamically, because of that i want to use it as this way. Is there another easy way to use my string active_mac in the query? Like "select epp_group_id from epp_inventory where epp_active_mac = " + active_mac

import os
import mysql.connector
from mysql.connector.constants import ClientFlag
import json


active_mac = "b45d50cfef6a"
active_mac_upper = active_mac.upper()
print(active_mac_upper)
mydb = mysql.connector.connect(
  host="localhost",
  user="VAadmin",
  password="991550sE*",
  database="VA"
)

mycursor = mydb.cursor()

sql = "select epp_group_id from epp_inventory where epp_active_mac = %s" 
adr = (active_mac_upper,)


mycursor.execute(sql, adr)

#mycursor.execute("select epp_group_id from epp_inventory where epp_active_mac = '%s'")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

You’ll need to single quote the MAC address. And case does not matter. I’d recommend using an f-string instead of string concatenation; it’ll be easier (and easier to read) to include the single quotes. — s3dev
– s3dev, Commented Jan 12, 2022 at 7:17
See the linked duplicate for how to safely inject values into SQL queries. Please do not use f-strings or any other string formatting technique: these are error-prone and expose your application to SQL injection attacks. — snakecharmerb
– snakecharmerb, Commented Jan 12, 2022 at 9:06

Gbox4 · Accepted Answer · 2022-01-12 07:22:11Z

0

SQL syntax requires single quotes when selecting columns that are strings.

Replace the query = line with the following:

query = f"select epp_active_ip, epp_hostname, epp_group_id from epp_inventory where epp_active_mac = '{active_mac.upper()}'"

answered Jan 12, 2022 at 7:22

Gbox4

6334 silver badges12 bronze badges

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

1 Comment

Risadinha Over a year ago

This solution does not prevent SQL injection. Depending on where the variable value is coming from (if it is uncontrolled user input), you invite trouble with this. It might be okish for the current situation but it should not be used in general.

Collectives™ on Stack Overflow

How can i use a string in mysql query in python? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related