/ ID: 1067 Title: Postgres Parameterized Queries 1067 Meta Description: How to Create Postgres Parameterized Queries. Code samples included. Meta Keywords: Postgres Parameterized Queries Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: PostgreSQL Parameterized Queries Python Status: Published /

Introduction

In this article, we'll learn to create and use Postgres parameterized queries for increased efficiency and security. Using PostgreSQL's parameterized query abilities will also provide increased security in relation to a very specific kind of hack called "SQL injection". We'll also look at stored procedures briefly, as their use in your PostgreSQL applications can afford similar benefits as executing SQL with parameters. We will make use of Python's "psycopg2" library to provide a database connection and other Postgres-related features. At first, we'll learn the use of psycopg2's execute function by exploring syntax and examples.

NOTE: Postgres parameterized queries require us to generate a prepared statement in Python. The interpreter brings in your parameters separately, and then executes the query in the PostgreSQL database.

Prerequisites

Python: Python version 2.5 or newer is required.
Postgres: Basic understanding of how to set up and use a Postgres database. Bonus points if you can write basic SQL queries.
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: Use your "pip" installer to add the psycopg2 library to your system's libraries and add this to the top of your python scripts when accessing Postgres:

Python psycopg2 framework

python import psycopg2

Python psycopg2 "execute" function

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

Analysis

With our first approach below, you will see use of the Python psycopg2 execute function without the optional "parameter values" you see in the above syntax. THEN we'll show how to use the Postgres parameterized query methodology, which we'll give you an example of later:

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_item FROM tbl_essential_items" s += "WHERE" s += "(" s += " t_name_item = '" + t_name_item + "'" s += ")" db_cursor.execute(s)

SQL Injection example

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:

"Authentic Elvis Presley blow up doll'; DROP TABLE tbl_cart;"

python # Remember: t_name_item = "Authentic Elvis Presley blow up doll'; DROP TABLE tbl_users;" s = "" s += "SELECT ID_item FROM tbl_essential_items" s += "WHERE" s += "(" s += " t_name_item = '" + t_name_item + "'" s += ")" db_cursor.execute(s)

Analysis

The user typed in a product name to be searched for, "Authentic Elvis Presley blow up doll", added a semicolon to instruct PostgreSQL to expect another command, and then the command, which is to delete (DROP) one of our tables (tbl_users)! How do we protect our application from this happening? We can start by "parameterizing", or "using parameters" in our queries. Later we'll look at another method; Stored Procedures.

Parameterized query for Postgres

python s = "" s += "SELECT ID_item FROM tbl_essential_items" s += "WHERE" s += "(" s += " t_name_item = (%t_name_item)" s += ", { s += " 't_name_item': t_name_item" s += " }" s += ")" db_cursor.execute(s)

The change you see in the SQL above sanitizes the value placed into "t_name_item" before it can cause harm to your PostgreSQL database. The psycopg2 cursor object treated the value of "t_name_item" 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 and which is just as secure, while being less prone to errors.

python s = "" s += "SELECT ID_item FROM tbl_essential_items" s += "WHERE" s += "(" s += " t_name_item = (%t_name_item)" s += ")" db_cursor. db_cursor.execute(s, [t_name_item])

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_essential_items" s += "(" s += "ID_item" s += ", t_name_item" s += ") VALUES (" s += ID_item s += ", '" + t_name_item + "'" 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_essential_items" s += "(" s += "ID_item" s += ", t_name_item" s += ") VALUES (" s += "(%ID_item)" s += ", (%t_name_item)" s += ", { s += " 'ID_item': ID_item" s += ",'t_name_item': t_name_item" s += "}" s += ")" db_cursor.execute(s)

Next, we'll use that optional parameter we showed you above that can be used with psycopg2's execute function:

python s = "" s += "INSERT INTO tbl_essential_items" s += "(" s += "ID_item" s += ", t_name_item" s += ") VALUES (" s += "(%ID_item)" s += ", '(%t_name_item)'" s += ")" db_cursor.execute(s, [ID_item, t_name_item])

You may have noticed now we are now sending two parameters ("ID_item" and "t_name_item") to the Postgres interpreter.

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

python list_products = ['sword', 'kwando', 'spear'] s = "" s += "INSERT INTO tbl_essential_items" s += "(" s += " t_name_item" s += ") VALUES (" s += " '(%list_products)'" s += ")" db_cursor.executemany(s, [ID_item, list_products])

Postgres Procedures

If you want to take your web database application to "the next level" in terms of efficiency, portability, scalability, and security, you may want to turn some oft-used processes into procedures. On competing database systems like MS SQL Server, they are called "Stored Procedures". Here's a brief breakdown of what PostgreSQL procedures are and how they work.

What is a Procedure for Postgres?

The first question you might ask is "Why not just use user-defined functions?" User-defined functions cannot execute transactions. So yeah, inside user-defined functions you cannot create a new transaction or commit or rollback the current transaction. Stored procedures that support transactions were introduced in Postgres 11.

Let's dive in to creating a Postgres Stored Procedure:

Postgres Stored Procedure Syntax

SQL CREATE OR REPLACE PROCEDURE [proc name]([optional:params]) IS [or "AS"] [DECLARE] -- variables BEGIN -- commands to run [EXCEPTION] -- error trapping END;

Stored Procedure parameters

In Postgres Procedures, there are three kind of parameters:

IN (only for input).
IN OUT (in/out mode - both input and output).
OUT (only for output).

Stored Procedure Example

Let's begin by creating a table named "tbl_tech" in Postgres. Our company uses this table to keep track of the coding and other technologies used in the company.

sql CREATE TABLE tbl_tech ( id serial NOT NULL, t_name varchar(64) NULL, t_category varchar(128) NULL, CONSTRAINT tbl_tech_pkey PRIMARY KEY (id) ); CREATE UNIQUE INDEX tbl_tech_id_idx ON tbl_tech USING btree (id);

Conclusion

You saw in this tutorial how to use a few kind of Postgres parameterized queries to increase security and efficiency for your web application! A few functions and commands we touched on include: execute and executemany, the list data type, and finally, how to create and use a Postgres stored procedure.