From: | Caleb Cushing <xenoterracide(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Feature request - psql --quote-variable |
Date: | 2017-03-07 20:04:40 |
Message-ID: | CAAHKNRFTaMxN5hV1CKVbKgNeSzg62fRuYryGJ7xNf0nXUUuTcw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
(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(dot)stehule(at)gmail(dot)com>
wrote:
> Hi
>
> 2017-02-21 15:19 GMT+01:00 Caleb Cushing <xenoterracide(at)gmail(dot)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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Dilger | 2017-03-07 20:24:56 | Running TAP regression tests under windows/msvc |
Previous Message | Adrian Klaver | 2017-03-07 15:04:25 | Re: is (not) distinct from |