/ ID: 915 Title: Python Parameterized SQL for Postgres 915 Meta Description: In this article we learn the ins and outs of Python parameterized SQL for Postgres. Code samples included. Meta Keywords: Python Parameterized SQL for Postgres Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: Python, Parameterized, SQL, Postgres, Injection Protection Status: Published /

Introduction

With this tutorial, we'll show you how to use Python Parameterized SQL for Postgres for greater security, primarily to give us a measure of SQL injection protection, if stored procedures are not suitable for the query. We'll be using Python's "psycopg2" library to supply us with the PostgreSQL database connection features needed. Here's how: To begin, we'll learn the syntax and use of psycopg2's execute function and later the executemany function. Next, below under "SQL Injection Protection", we study a basic SELECT query and look at how and why it can easily be hijacked to inject destructive code into the database application. THEN we show very similar Python code but with a parameterized SQL for selecting and eventually inserting Postgres data. Finally, we simplify even further by placing parameter values into our SQL via the second parameter in the execute function.

A parametrized query in Python is an operation which generates a prepared statement internally, then brings in your parameters and executes the SQL against the Postgres database.

Prerequisites

Postgres: Basic understanding of how Postgres works, failing that, at least a beginner-level understanding of SQL scripting.
Python: In this case, Python version 3 or above is needed. We use Visual Studio Code to write Python and Dbeaver to manage our Postgres database but please feel free to use whatever IDE you prefer.
Naming conventions: Optional and useful naming conventions tutorial explaining why we prefix our variables, column names, table names, etc. as you see us doing in this article. For example, naming "t_variable_name" with the "t_" you see at the beginning in order to delineate it as a "text" (string) object and "tbl_" before table names in order to clearly distinguish those objects as tables.
Psycopg2: Knowledge of how psycopg2's database cursor works and how the "execute" function works, which we'll begin with. Use your "pip" installer to add it to your system's libraries and in your code, add this at the top:

python import psycopg2

Python psycopg2 "execute" function

python db_cursor.execute(SQL, [parameter values])

Analysis

With our first two approaches below, you will see us use the Python psycopg2 cursor execute function without the optional "parameter values" you see in the above syntax. THEN we'll show how to use that methodology, which we'll give you a brief example of here:

SQL Injection Protection

What is SQL injection and why should we care? Look at the query below and we'll explain the vulnerability. Then we'll show you how to modify your script with Python to make it parameterized SQL for Postgres:

python s = "" s += "SELECT ID_product FROM tbl_products" s += "WHERE" s += "(" s += " t_name_product = '" + t_name_product + "'" s += ")" db_cursor.execute(s) dbRow = cur.fetchone() ID_product = dbRow(0)

How could a hacker use our Python/PostgreSQL application to inject disruptive code if we are using the SQL you see above?

One example is if the user were to type the following into a search box that gets relayed to that query:

``` python # user input gives the following value: t_name_product = "Bugs Bunny Doll'; DROP TABLE tbl_cart;"

s = ""
s += "SELECT ID_product FROM tbl_products"
s += "WHERE"
s += "("
s += " t_name_product = '" + t_name_product + "'"
s += ")"
db_cursor.execute(s)
dbRow = cur.fetchone()
ID_product = dbRow(0)

```

So they user typed in something (a product name) to be searched for, added a semicolon to tell Postgres to expect another command, and then supplies said command, which is to delete (DROP) one of our tables (tbl_cart)! How do we protect against this? We begin by "parameterizing", or "using parameters" the SQL:

python s = "" s += "SELECT ID_product FROM tbl_products" s += "WHERE" s += "(" s += " t_name_product = (%t_name_product)" s += ", { s += " 't_name_product': t_name_product" s += " }" s += ")" db_cursor.execute(s) dbRow = cur.fetchone() ID_product = dbRow(0)

The small change you see above sanitizes the value placed into "t_name_product" before it can cause harm to your PostgreSQL database. The cursor treated the value of "t_name_product" as a string and escaped any characters that might introduce Python SQL injection.

If the above format is confusing for you, we can offer another methodology, using Python psycopg2's "execute" function that might be easier to understand and use.

python s = "" s += "SELECT ID_product FROM tbl_products" s += "WHERE" s += "(" s += " t_name_product = (%t_name_product)" s += ")" db_cursor. db_cursor.execute(s, [t_name_product]) dbRow = cur.fetchone() ID_product = dbRow(0)

Now, let's look at how we would enact the same three changes, but with Postgres' INSERT command, for inserting new records into a database. Here's the non-parameterized way:

python s = "" s += "INSERT INTO tbl_products" s += "(" s += "ID_product" s += ", t_name_product" s += ") VALUES (" s += ID_product s += ", '" + t_name_product + "'" s += ")" db_cursor.execute(s)

And here is how we would use Python to parameterize our SQL for Postgres optimum safety. Oh and there's a bonus: Parameterized queries run faster!

python s = "" s += "INSERT INTO tbl_products" s += "(" s += "ID_product" s += ", t_name_product" s += ") VALUES (" s += "(%ID_product)" s += ", (%t_name_product)" s += ", { s += " 'ID_product': ID_product" s += ",'t_name_product': t_name_product" s += "}" s += ")" db_cursor.execute(s)

Next, we'll use that extra, optional parameter we talked about above that is available with Python's execute function:

python s = "" s += "INSERT INTO tbl_products" s += "(" s += "ID_product" s += ", t_name_product" s += ") VALUES (" s += "(%ID_product)" s += ", '(%t_name_product)'" s += ")" db_cursor.execute(s, [ID_product, t_name_product])

Here, you may have noticed now we are handing two parameters ("ID_product" and "t_name_product"), instead of one, over to Postgres' SQL engine.

Finally, we'll use executemany to INSERT a Python list (array) of values into the database:

python list_products = ['sword', 'kwando', 'spear'] s = "" s += "INSERT INTO tbl_products" s += "(" s += " t_name_product" s += ") VALUES (" s += " '(%list_products)'" s += ")" db_cursor.executemany(s, [ID_product, list_products])

INCREASE EFFICIENCY AND SECURITY EVEN MORE: Use Stored Procedures

Protect your user's data by learning more about

Conclusion

You learned in this tutorial how to use a couple types of Python parameterized SQL scripts for Postgres so as to increase security and efficiency for your web application! A few functions and commands we touched on include: execute and executemany, fetchone, and a brief use of the list data type, similar to a PostgreSQL array.