Re: [GENERAL] Feature request - psql --quote-variable - Mailing list pgsql-general

Thank you. Apparently I never saw this response, for some reason...

So reading that leaves me confused on one point, which is the right way to do it if you're inserting an  integer? would this be right? is there a difference between the single and double quotes here?

postgres=# create table foo(a int);
CREATE TABLE
Time: 276,386 ms
postgres=# insert into foo values('1');
INSERT 0 1
Time: 72,357 ms

(presume id is a bigint)
`insert into foo ( id ) values ( :'var' )`

maybe the docs should mention sql injection? (if for nothing more than google indexing and ctrl+f page searching)

 

On Tue, Feb 21, 2017 at 12:35 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

2017-02-21 15:19 GMT+01:00 Caleb Cushing <xenoterracide@gmail.com>:

recently while exploring this problem http://stackoverflow.com/q/40945277/206466. I decided to go with the docker container approach of a shell script.

 I realized that postgres' variables aren't quoted either, which results in me quoting them with bash, to help avoid accidents, and even then I'm not 100% sure I'm doing it right.

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 \
-v db="${POSTGRES_DB//\'/''}" \
-v user_changeset="${DB_USER_CHANGESET//\'/''}" \
-v user_readwrite="${DB_USER_READWRITE//\'/''}" \
-v user_readonly="${DB_USER_READONLY//\'/''}" \
-v pass_changeset="'${DB_PASS_CHANGESET//\'/''}'" \
-v pass_readwrite="'${DB_PASS_READWRITE//\'/''}'" \
-v pass_readonly="'${DB_PASS_READONLY//\'/''}'" \
--username "${POSTGRES_USER}" \
--dbname "${POSTGRES_DB}" \
--file="init-user.sql"

given the Popularity of Docker and that their are UI's to pass environment variables now (meaning the person doing so might not be a qualified "DBA", nor as trusted as they should be to have "root dba" access).  Even if the person is trusted, I feel like one shouldn't have to document "don't put quotes or SQL into your password" is an indication that something is wrong.

It would be nice to have some way to properly have these variables quoted.

1. provide a new argument name say --quote-variable (or -qv) and postgres will figure out how to quote based on the position of the variable

2. allow psql (or another app?) to provide an output quoter (since it has access to the lib) `pql -v user_changeset=$( psql --quote-string $DB_USER_CHANGESET )`, kind of a weird caller but basically allows you to pass an input to a function that does quoting properly

there might be other idea's too, these are just the ones I have.

Yes I know people who are able to manage such a container should be trusted... in theory though you can provide a UI that gives them access to manage the container with no actual access to the container. I don't actually have that problem it's more of a hypothetical to me, but I'm sure it will exist at some point.

Just sharing my pain in hopes that improvements can be developed.

[pavel@localhost ~]$ psql

Debug assertions "on"

psql (10devel)

Type "help" for help.

postgres=# \set var AHOJ

postgres=# \echo :var :'var' :"var"

AHOJ 'AHOJ' "AHOJ"

postgres=# 

https://www.postgresql.org/docs/9.2/static/app-psql.html looks to "SQL Interpolation"

Regards

Pavel

 

p.s. pg is still hashing its passwords with md5? :(

--

Caleb Cushing

http://xenoterracide.com

--

Caleb Cushing

http://xenoterracide.com