Skip site navigation (1) Skip section navigation (2)

Protection from SQL injection

From: "Thomas Mueller" <thomas(dot)tom(dot)mueller(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Protection from SQL injection
Date: 2008-04-28 18:55:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

As you know, "SQL injection" is the main security problem of databases today.

I think I found a solution: 'disabling literals'. Or you may call it
'enforcing the use of parameterized statements'. This means that SQL
statements with embedded user input are rejected at runtime. My
solution goes beyond saying "developers ~should~ use parameterized
statements". That is not a solution because developers are lazy. My
solution is: "developers MUST use parameterized statements". It goes
like this: Literals are disabled using the SQL statement:


Afterwards, SQL statements with text are not allowed any more for this
session. That means, SQL statement of the form "SELECT * FROM USERS
WHERE PASSWORD='qerkllkj'" will fail with the exception 'Literals are
not allowed, please use parameters'. It is like the database does not
know what ='qerkllkj' means. Only statements of the secure form are
allowed, for example "SELECT * FROM USERS WHERE PASSWORD=?". This
solves the problem because SQL injection is almost impossible if user
input is not directly embedded in SQL statements.

The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or
by an administrator. It is still possible to generate SQL statements
dynamically, and use the same APIs as before, as long as SQL
statements don't include literals. Literals can still be used when
using query tools, or in applications considered 'safe'. To ease
converting the application to use parameterized queries, there should
be a second mode where number literals are allowed: SET ALLOW_LITERALS
NUMBERS. To allow all literals, execute SET ALLOW_LITERALS ALL (this
is the default setting).

So far this feature is implemented in my little database H2. More
information about this feature is described here:

I know about the Perl taint mode, but this is only for Perl. I also
know about disabling multi-statement commands (only solves part of the
problem). PostgreSQL should also support database level 'constants'
that are similar to constants in other programming languages,
otherwise application level constants (such as 'active') can't be used
in queries directly (I propose to add new SQL statements CREATE
STATE_ACTIVE VALUE 'active'). I also know the 'disabling literals'
feature does not solve SQL injection completely: for example 'ORDER BY
injection' where an application dynamically adds the column to sort on
based on a hidden 'sort column' field in a web app. To solve that I
suggest to support parameterized ORDER BY: ORDER BY ? where ? is an
integer. Then, instead of using SET ALLOW_LITERALS NONE the use of
literals should probably be two access right (REVOKE LITERAL_TEXT,
LITERAL_NUMBER FROM APP_ROLE). Those are details that still need to be

What do you think about it? Do you think it makes sense to implement
this security feature in PostgreSQL as well? If not why not? Does
PostgreSQL have another solution or plan to solve the SQL injection


P.S. I have send this proposal to pgsql-sql(at)postgresql(dot)org first and
got replies, but I would like to get some feedback from the PostgreSQL
developers as well.


pgsql-hackers by date

Next:From: Josh BerkusDate: 2008-04-28 19:09:29
Subject: Re: Protection from SQL injection
Previous:From: Martijn van OosterhoutDate: 2008-04-28 18:10:17
Subject: Re: we don't have a bugzilla

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group