MySQL request in Python

Question

In my code I have three requests to a MySQL database:

@app.route('/private', methods=['POST'])
def private():
 login = request.form['login']
 if login is None or not login:
 return jsonify(data='Incorrect URL')
 try:
 c, conn = cursor_connection()
 c = conn.cursor()
 c.execute("SELECT accounts_info_uid "
 "FROM auth_info WHERE login='{}' ".format(login))
 id = c.fetchall()
 if not id:
 return jsonify(data='Incorrect login')
 c.execute("SELECT * FROM boxes_id AS tb1 LEFT JOIN"
 " accounts_info AS tb2 ON tb2.boxes_ids=tb1.uid "
 # "LEFT JOIN electricity_info as tb3 ON tb3.boxes_id_uid=tb1.uid"
 " WHERE tb2.uid={} ".format(id[0][0]))
 uid, mc_address, working_status, activation_status, _,\
 first_name, second_name, registration_date, phone, email, boxes_id = c.fetchall()[0]
 c.execute(" SELECT consumed_electricity "
 "FROM electricity_info "
 "WHERE boxes_id_uid={} ".format(boxes_id))
 consumed_electricity = [float(val[0]) for val in c.fetchall()]
 c.close()
 conn.close()
 except Exception as e:
 logger.error(msg='Cannot execute /private {}'.format(e))
 return str(e)

I fetched a list from electricity info by primary key in boxes_id (so in electricity_info it is called boxes_id_uid).

Structure of pk in my tables:

auth_info --------> pk is accounts_info_uid
boxes_id ----------> pk is uid
accounts_info ------> pk is uid and it is connected to table boxes_id by field boxes_id
electricity_info ------> pk is boxes_id_uid

I think it can be optimized in one SQL request. If so, can you tell me how to achieve that?

• Gareth Rees: This is full of SQL injection vulnerabilities.
• John: Thanks! Do you know how to fix it and optimize it?
• Richard Neumann: @John Use a ORM framework like peewee.
• John: If I change format() to %s will it solve the issue?

Answer 1

1. DONT! String format your sql queries, but let the cursor do it for you.

   As @Gareth Rees said, this is not secure!

   c.execute("SELECT accounts_info_uid "
    "FROM auth_info WHERE login='{}' ".format(login))
   

   As taken from the docs, this would be the proper way to execute statements with sql:

   cursor.execute("SELECT accounts_info_uid FROM auth_info WHERE login = %s", login)
   

2. You could use a context manager over the connection

   Using a context manager would improve the readability, see PEP343

   There are some resource out there how to implement such a thing

3. Make the login a separate function

4. I think it can be optimized in one SQL request?

   Yes, but without the database itself it is hard to test, a github repo or some more information would help.

   However you could improve on your google-fu, Simply typing "mysql join 3 tables" would have given you the answer you are looking for.

   https://stackoverflow.com/questions/3709560/joining-three-tables-using-mysql

• John: Thank you! But I asked to show me the way to combine my three sql-requests into just one.. Is it possible?