PostgreSQL Protocol: Server-Side Prepared Statements

Explore how PostgreSQL uses server-side prepared statements to separate query text from parameters, improving security and preventing SQL injection. This lesson helps you understand the extended query protocol, including parse, bind, and execute messages, with practical Python examples using the asyncpg driver to implement safe, efficient database queries.

We'll cover the following...

Extended query
Example
Try it yourself

It’s possible to send the query string and its arguments separately on the wire by using server-side prepared statements. This is a pretty common way to do it, mostly because PQexecParams isn’t well-known, though it made its debut in PostgreSQL 7.4 in 2003. To this day, a lot of PostgreSQL drivers still don’t expose the PQexecParams facility, which is unfortunate.

Server-side prepared statements can be used in SQL thanks to the prepare and execute commands syntax, as in the following example:

PostgreSQL

begin;
create table factbook
 (
   year    int,
   date    date,
   shares  text,
   trades  text,
   dollars text
 );
-- datestyle of the database to ISO, MDY
\copy factbook from 'factbook.csv' with delimiter E'\t' null ''
alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,
   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,
   
   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;
commit;
prepare foo as
 select date, shares, trades, dollars
   from factbook
  where date >= $1::date
    and date  < $1::date + interval '1 month'
  order by date;
execute foo('2010-02-01');