Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
User Input to mySQL database
#1
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 %}
Reply
#2
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
(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
Reply
#4
(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'))
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Send email to gmail after user fill up contact form and getting django database updat Man_from_India 0 2,070 Jan-22-2020, 03:59 PM
Last Post: Man_from_India
  MySQL Database Flask maurosmartins 0 1,787 Oct-03-2019, 10:56 AM
Last Post: maurosmartins
  Download images generated by user input one_of_us 0 2,465 Mar-26-2019, 07:58 AM
Last Post: one_of_us
  How to format a datetime MySQL database field to local using strftime() nikos 6 3,720 Feb-24-2019, 06:32 PM
Last Post: nikos
  mysql database error brecht83 1 4,972 Dec-14-2018, 01:25 PM
Last Post: jeanMichelBain
  Flask: Cookies for Saving User Input ? jomonetta 2 3,478 Nov-03-2018, 10:47 AM
Last Post: j.crater
  [Flask] Create new project for add user,edit,delete,view users in python with mysql connector chandranmanikandan 0 7,294 Oct-30-2018, 10:19 AM
Last Post: chandranmanikandan
  Pulling any information from a dictionary with a user input Darmanus 19 8,620 Nov-22-2017, 08:56 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020