3
\$\begingroup\$

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?

\$\endgroup\$
  • 4
    \$\begingroup\$ This is full of SQL injection vulnerabilities. \$\endgroup\$ – Gareth Rees Feb 2 '18 at 11:51
  • \$\begingroup\$ Thanks! Do you know how to fix it and optimize it? \$\endgroup\$ – John Feb 2 '18 at 12:34
  • \$\begingroup\$ @John Use a ORM framework like peewee. \$\endgroup\$ – Richard Neumann Feb 2 '18 at 13:10
  • \$\begingroup\$ If I change format() to %s will it solve the issue? \$\endgroup\$ – John Feb 2 '18 at 18:23
3
\$\begingroup\$
  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

\$\endgroup\$
  • \$\begingroup\$ Thank you! But I asked to show me the way to combine my three sql-requests into just one.. Is it possible? \$\endgroup\$ – John Feb 3 '18 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.