2017-03-07 21:04 GMT+01:00 Caleb Cushing <xenoterracide@xxxxxxxxx>:
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.stehule@xxxxxxxxx> wrote:Hi2017-02-21 15:19 GMT+01:00 Caleb Cushing <xenoterracide@xxxxxxxxx>: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 variable2. 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 properlythere 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 ~]$ psqlDebug assertions "on"psql (10devel)Type "help" for help.postgres=# \set var AHOJpostgres=# \echo :var :'var' :"var"AHOJ 'AHOJ' "AHOJ"postgres=#https://www.postgresql.org/docs/9.2/static/app-psql.html looks to "SQL Interpolation"RegardsPavel--Caleb Cushing
