How to implement a search function using a raw SQL query

Question

I am creating an app guided by CS50's web series, which requires me to ONLY use raw SQL queries not ORM.

I am trying to make a search function where a user can look up the list of books that's stored in the database. I want to able them to query ISBN, title, author column in the table called 'books'

Currently, it does shoot a 'GET' request with no problem but it is not returning any data and I think the problem is at the SQL line I've scripted.

Here's the route:

@app.route("/", methods=['GET','POST'])
def index():
    # search function for books
    if request.method == "GET":
        searchQuery = request.form.get("searchQuery")
        # return value from the search
        searchResult = db.execute("SELECT isbn, author, title FROM books WHERE isbn LIKE '%"+searchQuery+"%' OR author LIKE '%"+searchQuery+"%' OR title LIKE '%"+searchQuery+"%'").fetchall()
        # add search result to the list
        session["books"] = []
        # add the each result to the list
        for i in searchResult:
            session["books"].append(i)
        return render_template("index.html", books=session["books"])
    return render_template("index.html")

and here's my template.

    <form method="GET">
        <input type="text" name="searchQuery" class="searchTerm" placeholder="What are you looking for?">
        <button type="submit" class="searchButton">submit</button>
    </form>
    <div>
        <h3>
            {% for book in books %}
            {{ book }}
            {% endfor %}
        </h3>
    </div>

Can anyone spot the problem, please? Please note that I am supposed to utilize the raw SQL queries and session.

Answer 1

I created a github with full solution for you :)

https://github.com/researcher2/stackoverflow_57120430

A couple of things:

Avoiding SQL Injection

I recommend using bindings when doing raw sql statements, my code reflects that. I spent ages trying to get this working with your statement before stumbling upon this:

Python SQLite parameter substitution with wildcards in LIKE

Basically you can't put bindings inside the LIKE '%?%' because the quotes cause the replacement token to be ignored.

Instead you just have to do LIKE ? and build the replacement manually.

Using Session

All session information is JSON serialized and then sent to the client. In this case the row records weren't JSON serializable. This showed up as an error for me:

TypeError: Object of type 'RowProxy' is not JSON serializable

I probably wouldn't use the session here as there is no need for the client to be aware of this, as you're going to build them a nice html page with the information anyway. Just use a python dictionary and pass it to the template engine. My code did use the session because this is what you started with.

In case github ever goes down:

from flask import request, render_template, session
from app import app, db

@app.route("/", methods=['GET','POST'])
def index():
    if request.method == "POST":
        searchQuery = request.form.get("searchQuery")
        print(searchQuery)

        # Avoid SQL Injection Using Bindings
        sql = "SELECT isbn, author, title \
               FROM book \
               WHERE isbn LIKE :x \
               OR author LIKE :y \
               OR title LIKE :z"

        # I spent an hour wondering why I couldnt put the bindings inside the wildcard string...
        # https://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like
        matchString = "%{}%".format(searchQuery)

        stmt = db.text(sql).bindparams(x=matchString, y=matchString, z=matchString)

        results = db.session.execute(stmt).fetchall()
        print(results)

        session["books"] = []

        for row in results:
            # A row is not JSON serializable so we pull out the pieces
            book = dict()
            book["isbn"] = row[0]
            book["author"] = row[1]
            book["title"] = row[2]
            session["books"].append(book)
        return render_template("index.html", searchedFor=searchQuery, books=session["books"])

    return render_template("index.html")

Answer 2

Thanks for the chosen answer. The problem was solved with one simple change.

I needed to swap 'LIKE' to 'ILIKE' in the SQL query line.

searchResult = db.execute(
    """
    SELECT isbn, author, title
    FROM books
    WHERE isbn ILIKE :search OR author ILIKE :search OR title ILIKE :search
    """,
    {"search": "%"+searchQuery+"%"}
).fetchall()

This may not be an elegant solution but it did solve a temporary problem. Again, kudos for the guy who answered and create a github repo for everyone else who would encounter the problem while doing CS50's web series!