How To Implement A Search Function Using A Raw Sql Query

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

Solution 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: Objectoftype'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'])defindex():
    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")

Solution 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!