Unable to execute a query on flask using MqSQL

Hello there im trying to execute a SQL query using Flask with mysqldatabase, the query returns as a JSON managed my JQuery on the front end. The goal is to use a search bar to find any matches with the data base. The query works just fine if I use just one “like” sentence, for example.

query = "SELECT terapia.*,terapeuta.nombre,cliente.nombres, especialidad.descripcion FROM terapia LEFT JOIN terapeuta ON terapeuta.id_terapueta = terapia.terapeuta_id_terapueta LEFT JOIN cliente ON cliente.id_cliente = terapia.cliente_id_cliente LEFT JOIN especialidad ON especialidad.id_especialidad = terapeuta.id_especialidad WHERE especialidad.descripcion LIKE '{}%'".format(searchbox)

But in the moment I use and “OR” sentence for multiple search like this:

query = "SELECT terapia.*,terapeuta.nombre,cliente.nombres, especialidad.descripcion FROM terapia LEFT JOIN terapeuta ON terapeuta.id_terapueta = terapia.terapeuta_id_terapueta LEFT JOIN cliente ON cliente.id_cliente = terapia.cliente_id_cliente LEFT JOIN especialidad ON especialidad.id_especialidad = terapeuta.id_especialidad WHERE especialidad.descripcion LIKE '{}%' or terapeuta.nombre LIKE '{}%'".format(searchbox)

The query does not work at all and outputs this error

error for jQuery

Curious enough executing the same query on Dbebaber works just fine, obviously replacing ‘{}%’ whit ‘string%’, I have no idea why the query refuses to work, in addition I’m including the full python and Jquery code.

Flask:

from flask import render_template, request, redirect, url_for, session, flash, jsonify
@terapia.route('/livesearch', methods=["POST","GET"])
def livesearch():
    searchbox = request.form.get('text')
    cur = mysql.connection.cursor()
    query = "SELECT terapia.*,terapeuta.nombre,cliente.nombres, especialidad.descripcion FROM terapia LEFT JOIN terapeuta ON terapeuta.id_terapueta = terapia.terapeuta_id_terapueta LEFT JOIN cliente ON cliente.id_cliente = terapia.cliente_id_cliente LEFT JOIN especialidad ON especialidad.id_especialidad = terapeuta.id_especialidad WHERE especialidad.descripcion LIKE '{}%' or terapeuta.nombre LIKE '{}%'".format(searchbox)
    cur.execute(query)
    result = cur.fetchall()
    return jsonify(result)

HTML and JQUERY

<div class="col-md-12">
        <input type="text" id="livebox">
        <p id="search-response"></p>
    </div>
    <script>
        $(document).ready(function(){
            $("#livebox").on("input", function(e){
                let textinlivebox = $("#livebox").val();
                $.ajax({
                    method: "POST",
                    url: "/terapia/livesearch",
                    data:{text: textinlivebox},
                    success: function(res){
                        //document.getElementById('search-response').innerHTML = res;
                        console.log(res)
                    }
                })
            });
        });
    </script>

Any help or advice is welcome.

Answer

Please read up on SQL injection vulnerabilities, and use:

query = 'SELECT ... FROM ... WHERE especialidad.descripcion LIKE %s or terapeuta.nombre LIKE %s'
cur.execute(query, (searchbox, searchbox))

Which will escape user input, so malicious users cannot write SQL commands into the searchbox.

Advertisement

Answer