Flask: Dropdown values based on prior selection

Question

I'm trying to limit selections in a dropdown based on a prior selection the user has made. This is how my flask looks:

init.py

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID']) #displayed invoicereceiver
    global sessioncur
    try:
        form = CreateinvoiceForm(request.form)
        if request.method == 'POST' and form.validate():
            #HEADER
            #This fetches from HTML
            customer = request.form.get('customer')
            goodsrec = request.form.get('goodsrec')
    return render_template("createinvoice.html", form=form,  mySQL2 = mySQL2)

customer is populated from an html form using mySQL2 as possible variables to select from:

html select form

<select required name="customer" class="selectpicker form-control" , 
placeholder="Select">
<option selected="selected"></option>
{% for o in mySQL2 %}      
<option value="{{ o[2] }}">{{ o[2] }}</option>  
{% endfor %}
</select>

The selection for goodsrec has to dependend on which customer was selected. My idea was to obtain the customer ID doing as follows:

c, conn = connection()
customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" +
str(customer) +"' limit 1")
customerID = c.fetchone()[0]

This value I could then use in a function I have to obtain the goodsreceivers with that ID:

def SelectGoodsrecSEE(customerID):
    c,conn = connection()
    c.execute("SELECT * FROM goodsrec WHERE Gr_Cm_id=" +str(id))
    mySQL8 = c.fetchall()
    c.close()
    conn.close()
    gc.collect()
    return mySQL8

So far I am quite sure that would work. What I don't know is how to structure the flask to make it load the first selection and take it into account for the second one. Similiar to the html I would have to loop through mySQL8. But how does the strucutre look in flask to get that done? At the moment what I have looks like

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID']) #displayed invoicereceiver
    global sessioncur
    try:
    form = CreateinvoiceForm(request.form)
    if request.method == 'POST' and form.validate():
        #HEADER
        #This fetches from HTML
        customer = request.form.get('customer')
        c, conn = connection()
        customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" +
        str(customer) +"' limit 1")
        customerID = c.fetchone()[0]
        mySQL8 = SelectGoodsrecSEE(customerID)
        goodsrec = request.form.get('goodsrec')
    return render_template("create.html", form=form,  mySQL2 = mySQL2)

I need to be able to pass mySQL8 to the create.html, so that I can make the selection from it in html. Any ideas? Hope its more or less clear what I'm looking for..

EDIT

SELECT * FROM goodsrec WHERE Gr_Cm_id=18; mySQL8

Answer 1

SQL Injection Risk

First and foremost you should improve your SQL code because as you have it now you are vulnerable to SQL Injection attacks. So, instead of:

c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" + str(customer) + "' limit 1")

the recommended usage is:

sql = 'SELECT Cm_Id FROM customer WHERE Cm_name = %s LIMIT 1'
parameters = [str(customer)]
c.execute(sql, parameters)

a couple additional SO posts that discuss this issue:

• Python best practice and securest to connect to MySQL and execute queries
• Python MySQL Parameterized Queries

Implementing Cascading Selects

Python:

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID'])
    global sessioncur
    try:
        form = CreateinvoiceForm(request.form)
        if request.method == 'POST' and form.validate():
            customer = request.form.get('customer')
            goodsrec = request.form.get('goodsrec')
            # do stuff with submitted form...
    return render_template("createinvoice.html", form=form,  mySQL2 = mySQL2)


@app.route('/get_goods_receivers/')
def get_goods_receivers():
    customer = request.args.get('customer')
    print(customer)
    if customer:
        c = connection()
        customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name = %s LIMIT 1", [customer])
        customerID = c.fetchone()[0]
        print customerID
        c.execute("SELECT * FROM goodsrec WHERE Gr_Cm_id = %s", [customerID])
        mySQL8 = c.fetchall()
        c.close()
        # x[0] here is Gr_id (for application use)
        # x[3] here is the Gr_name field (for user display)
        data = [{"id": x[0], "name": x[3]} for x in mySQL8]
        print(data)
    return jsonify(data)

HTML/Javascript:

<select name="customer" id="select_customer" class="selectpicker form-control">
    <option selected="selected"></option>
    {% for o in mySQL2 %}
    <option value="{{ o[2] }}">{{ o[2] }}</option>
    {% endfor %}
</select>

<select name="goodsrec" id="select_goodsrec" class="selectpicker form-control" disabled>
    <option>Select a Customer...</option>
</select>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script charset="utf-8" type="text/javascript">
    $(function() {
        var dropdown = {
            customer: $('#select_customer'),
            goodsrec: $('#select_goodsrec')
        };

        // function to call XHR and update goodsrec dropdown
        function updateGoodsrec() {
            var customer = dropdown.customer.val();
            dropdown.goodsrec.attr('disabled', 'disabled');
            console.log(customer);

            if (customer.length) {
                dropdown.goodsrec.empty();
                $.getJSON("{{ url_for('get_goods_receivers') }}", {customer: customer}, function(data) {
                    console.log(data);
                    data.forEach(function(item) {
                        dropdown.goodsrec.append(
                            $('<option>', {
                                value: item.id,
                                text: item.name
                            })
                        );
                    });
                    dropdown.goodsrec.removeAttr('disabled');
                });
            }
        }

        // event listener to customer dropdown change
        dropdown.customer.on('change', function() {
            updateGoodsrec();
        });

    });
</script>