0

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)
nuurrss
  • 5
  • 4
  • 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 Jan 12 '22 at 07:17
  • 1
    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 Jan 12 '22 at 09:06
  • see also https://stackoverflow.com/questions/33193809/python-mysql-connector-select-with-variable – Risadinha Jan 12 '22 at 12:33

1 Answers1

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()}'"
Gbox4
  • 623
  • 2
  • 10
  • 1
    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. – Risadinha Jan 12 '22 at 12:28