Python Forum

Hi,

I have some issues while writing my query and specially how to "secure" de user_input in the query
I'm trying to use the user input and pass it in a query to get some results.

I have this Error:

MySQLdb._exceptions.ProgrammingError
MySQLdb._exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'%"user_input"%\n\t\t\t\t\t\tor f.family_address like %"user_input"%\n\t\t\t\t\t\tORDER BY family_name\' at line 3')

Any help to improve my code?

#home.html

    <p class="article-content"> 
      <div class="form_form">
      <form class="form" method="post" action="/search">
        <label for="user_input"></label>
        <input id="user_input" name="user_input" type="text">
        <input type = "submit" value = "send">
      </form>  
    </div>   
    </p>

#routes.py

@app.route('/search', methods=['GET', 'POST'])
def search():
    if request.method == "POST":
        user_input = request.form["user_input"]  

        cur = db.connection.cursor()

        query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")

        cur.execute(query)

        results = cur.fetchall()
        
        return render_template('search_results.html', user_input=user_input, results=results)
        
    else:

        return redirect(url_for('home'))

#search_results.html

{% extends "layout.html" %}
{% block content %}
<article class="media content-section">
  <div class="media-body">
    <div class="article-metadata">
      <h5><a class="mr-2" href="#">results for{{ user_input }}</a></h5>
    </div>
    <p class="article-content"><p>Family Name: </p>{{ results.name }}</p>
    <p class="article-content"><p>Family Description: </p>{{ results.description }}</p>
    <p class="article-content"><p>Address: {{ results.address }}</p>
    <p class="article-content"><p>Phone Number: {{ results.phone }}</p>
  </div>
</article>


<form>
  <input type="button" value="New Search" onclick="history.go(-1)">
</form>

{% endblock content %}

your line 8

query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")

should raise an error

Error:
>>> user_input = 'SPAM'
>>> query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: bad operand type for unary +: 'str'

check the quotes. ALso note that what you do is prone to cause you problems. You should use parametrized query, not concatenate user input. this will open your code to sql injections


and there is no need of using brackets on this line

(Aug-25-2020, 01:16 PM)buran Wrote: [ -> ]your line 8

query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")

should raise an error

Error:
>>> user_input = 'SPAM'
>>> query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: bad operand type for unary +: 'str'

check the quotes. ALso note that what you do is prone to cause you problems. You should use parametrized query, not concatenate user input. this will open your code to sql injections


and there is no need of using brackets on this line

ok I'll how to use parametrized query and remove the brackets. Thks

(Aug-25-2020, 01:16 PM)buran Wrote: [ -> ]your line 8

query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")

should raise an error

Error:
>>> user_input = 'SPAM'
>>> query = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Family f WHERE f.family_name like "%+user_input+"% or f.family_address like "%"+user_input+"%" ORDER BY family_name")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: bad operand type for unary +: 'str'

check the quotes. ALso note that what you do is prone to cause you problems. You should use parametrized query, not concatenate user input. this will open your code to sql injections


and there is no need of using brackets on this line

This is what I did but got a new Error and tried to fix it changing the position of "cur=db.connection.cursor()" but nothing

MySQLdb._exceptions.ProgrammingError
MySQLdb._exceptions.ProgrammingError: execute() first

@app.route('/search', methods=['GET', 'POST'])
def search():

    cur = db.connection.cursor()

    if request.method == "POST":
        
        user_input = request.form["user_input"]  

        cur.execute = ("SELECT f.family_name, f.family_description, f.family_address, f.family_phone FROM Shop f WHERE f.family_address LIKE %s ORDER BY family_name", ( "%" + user_input + "%",))

        results = cur.fetchall()
        
        return render_template('search_results.html', user_input=user_input, results=results)
        
    else:

        return redirect(url_for('home'))