如何使用原始SQL查询实现搜索功能

Question

我正在根据CS50的网络系列创建一个应用程序，该程序要求我仅使用原始SQL查询而不是ORM。

我正在尝试提供一个搜索功能，用户可以在其中查找存储在数据库中的书籍列表。我想让他们查询名为“ books”的表格中的ISBN，标题，作者列

目前，它确实拍摄了“ GET”请求，没有问题，但是它没有返回任何数据，我认为问题出在我编写的SQL行中。

这是路线：

@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")

这是我的模板

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

任何人都可以发现问题吗？请注意，我应该利用原始SQL查询和会话。

Answer 1

我为您创建了一个具有完整解决方案的github :)

https://github.com/researcher2/stackoverflow_57120430

有两件事：

避免SQL注入

我建议在执行原始sql语句时使用绑定，我的代码反映了这一点。我花了很长时间尝试使它与您的陈述相符，然后才绊倒于此：

在LIKE中使用通配符替换Python
SQLite参数

基本上，您不能将绑定放在LIKE’％？％’内，因为引号会导致替换令牌被忽略。

相反，您只需要执行LIKE？并手动建立替代品。

使用会议

所有会话信息都是JSON序列化的，然后发送到客户端。在这种情况下，行记录不是JSON可序列化的。这对我来说是一个错误：

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

我可能不会在这里使用该会话，因为客户端不需要知道这一点，因为无论如何您将用信息来构建一个漂亮的html页面。只需使用python字典并将其传递给模板引擎即可。我的代码确实使用了会话，因为这是您开始的过程。

万一github崩溃了：

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")