| Lists: | pgsql-general |
|---|
| From: | Alan Gutierrez <alan(at)prettyrobots(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Escaping `psql --variable` |
| Date: | 2012-05-29 22:32:54 |
| Message-ID: | 20120529223254.GB3732@gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
|---|---|
| To: | Alan Gutierrez <alan(at)prettyrobots(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Escaping `psql --variable` |
| Date: | 2012-05-30 00:19:49 |
| Message-ID: | 1338337189.28651.25.camel@sussancws0025 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Alan Gutierrez <alan(at)prettyrobots(dot)com> |
|---|---|
| To: | Jeff Davis <pgsql(at)j-davis(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Escaping `psql --variable` |
| Date: | 2012-05-30 02:20:55 |
| Message-ID: | 20120530022055.GB7607@gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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