Python function to work with dynamic SQL Queries

Hi community, I'm new here and well, relatively new to development too! I aspire to be a full stack developer one day and I'm looking forward to all the inspiration, encouragement and guidance along the way.

---

The Problem

Today at work I came across a really interesting requirement, which honestly seemed to be challenging at first but was more of an eye opener for me about my very static approach to SQL queries. Anytime I was expected to query a database, this is how I would do it:

SELECT * FROM details WHERE firstName='John' AND lastName='Doe';

What I want mean is, I would use a static query, because I'd always be aware of the parameters ( the column names, the column values and the different clauses too! ) but today, I was expected to search across the table based solely on the user input i.e search across a specific table without knowing anything about the entries in that table or the search query. At first, the very notion of using the user inputs as search parameters baffled me. I wasn't aware of the possibility of working with dynamic SQL queries (hey, in my defence, I'm just a amateur backend developer) but since it was a non-negotiable requirement, I decided to take a shot at it.

My approach towards solving the problem

For this particular project, I am working with:

1. Django (Python web framework - Backend)
2. PostgreSQL (RDBMS - Database)
3. React (JavaScript Library - Frontend)

The project I am working on is fairly complex, so I'll try to keep the process flow as simple as I can and focus more on the actual logic to solve the problem at hand. The user inputs are captured in a form of sorts with various possible searchable field names alongside a search box for each field. These are then sent to the backend as a query object, when the user hits the search button as follows:

const res= await axios.get("http://127.0.0.1:8000/application/search/?query=" + JSON.stringify(queryObj));

In case you are curious to know what the query object looks like, here's a possible search query example generated from the inputs-
First Name: John
Last Name: Doe

let queryObj={firstName:"John", lastName:"Doe",};

In the backend(inside the Django application\views.py), the queryObj is processed as follows:

from django.http import JsonResponse
import psycopg2
import ast
def search_table(request):
  search_query=request.GET.get('query')
  queryparam=ast.literal_eval(search_query)

Just in case you are wondering why I have used the built-in function ast.literal_eval(search_query) here, it is because the search_query is a string object and not a python dictionary of key-value pairs. This function helps converting the string object keys in search_query to actual python dictionary keys.

Moving on, establishing a connection with the postgresql database using psycopg2:

from django.http import JsonResponse
import psycopg2
import ast
def search_table(request):
    search_query=request.GET.get('query')
    queryparam=ast.literal_eval(search_query)
    con=psycopg2.connect(database="database_name",user="user_name",password="pwd",host="0.0.0.0",port="12345")
    cursor=con.cursor()
    and_clause=[]
    for k,v in queryparam.items():
        and_clause.append("%s = '%s'" % (k,v))
    and_clause_str=' AND '.join(and_clause)
    sql_query='SELECT * FROM table_name WHERE ' + and_clause_str
    print(sql_query)
    cursor.execute(sql_query)
    result=cursor.fetchall()
    return JsonResponse(result)

So, the code here is pretty self explanatory, but just to quickly walk you through the logic -

I have declared a list of strings named and_clause which will store the query_param dictionary keys to be used as column names and query_param dictionary values as the actual value to be searched for seperated by = to structure it like a SQL query.

If there is more than one entry in the query_param dictionary i.e when the user wants to search based on multiple parameters or columns, I join these strings using 'AND' and store it in a new string variable called and_clause_str.

This is how the final SQL statement will look:

Key points to consider

1. The function right now doesn't generate entirely dynamic queries. If you would have noticed I still do specify the table name and also have a mandatory where clause. I have this specifically for two reasons:

   • The project I am working on aims to perform search operations across databases so the 'where' clause needs to be present as it acts as the search parameter.
   • The table name in my project, is going to be a very standard and common table name, which will be maintained as the same name across different databases, thus I have a query with a hard-coded table name.

2. My understanding on SQL Injections is still very poor and I agree this code might be prone to SQL injections, I do plan on optimising it further to prevent any possibility of being prone to such attacks.

3. In case you are wondering, what if the user provided input isn't an actual column name in the table, then I'd like to let you know that form column fields are generated from the backend and sent to the frontend to enable search. So in a way, only the existing column names are populated.

---

I hope I did justice to my very first blog post! I really hope this proves to be useful or something new to anyone reading this. I am open to suggestions on further enhancing the code and would love to hear your thoughts or questions on dynamic SQL queries.

Thank you for reading!