Thread: Escaping `psql --variable`

Surprised that this works:

     echo ":foo" | psql --variable foo="SELECT 1 AS FOO;"  template1

Why doesn't `psql` escape parameters passed in through `--variable`. When I use
a library in other languages, they will escape the variable.

How do I use `psql` from `bash` so that it will escape variables and thwart SQL
injection?

--
Alan Gutierrez - @bigeasy

On Tue, 2012-05-29 at 18:32 -0400, Alan Gutierrez wrote:
> Surprised that this works:
>
>      echo ":foo" | psql --variable foo="SELECT 1 AS FOO;"  template1
>
> Why doesn't `psql` escape parameters passed in through `--variable`. When I use
> a library in other languages, they will escape the variable.
>
> How do I use `psql` from `bash` so that it will escape variables and thwart SQL
> injection?

http://www.postgresql.org/docs/9.1/static/app-psql.html#APP-PSQL-VARIABLES

In particular, look at the section on SQL Interpolation. Hopefully that
answers your question.

Regards,
    Jeff Davis

On Tue, May 29, 2012 at 05:19:49PM -0700, Jeff Davis wrote:
> On Tue, 2012-05-29 at 18:32 -0400, Alan Gutierrez wrote:
> > Surprised that this works:
> >
> >      echo ":foo" | psql --variable foo="SELECT 1 AS FOO;"  template1
> >
> > Why doesn't `psql` escape parameters passed in through `--variable`. When I use
> > a library in other languages, they will escape the variable.
> >
> > How do I use `psql` from `bash` so that it will escape variables and thwart SQL
> > injection?
>
> http://www.postgresql.org/docs/9.1/static/app-psql.html#APP-PSQL-VARIABLES
>
> In particular, look at the section on SQL Interpolation. Hopefully that
> answers your question.
>
> Regards,
>     Jeff Davis
>

Yes. Thank you. To escape a variable and thwart SQL injection:

cat <<SQL | psql --variable name="Robert'); DROP TABLE Students; --" school
INSERT INTO Students(name) VALUES(:'name')
SQL

The single quotes around name will escape the psql variable as an SQL string.

--
Alan Gutierrez - @bigeasy