Re: Feature request - psql --quote-variable

2017-03-07 21:04 GMT+01:00 Caleb Cushing <xenoterracide(at)gmail(dot)com>:

> 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' )`
>

double quotes are used for identifiers. 'xxxx' is string literal, "xxxx" is
sql identifier like table name or column name.

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

This is psql client side feature - where SQL injection is possible, but the
risk is usually low - more significant are errors coming from missing or
wrong value escapeing.

Currently in patch pool is a patch, that enable possibility to use
parametrized queries from psql - it can be another way, how to execute
query safely.

Any documentation enhancing is good. If you have a idea, please, send a
text.

Regards

Pavel

>
>
> 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
>
> http://xenoterracide.com
>