Skip to content
Advertisement

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

and here’s my template.

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

Advertisement

Answer

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:

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:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement