Thread: How does one make the following psql statement sql-injection resilient?
How does one make the following psql statement sql-injection resilient?
From
"David G. Johnston"
Date:
psql "$SERVICE" \
--echo-queries \
--set=string_input="${1:-ok_to_return}" \
--set=start="${2:-5}" \
--set=end="${3:-10}" \
<<'SQL'
SELECT idx
FROM generate_series(1, 20) gs (idx)
WHERE 'short-circuit' != :'string_input'
AND idx BETWEEN :start AND :end;
SQL
# (6 rows)
--set=end="${3:-10 AND false}"
# (0 rows)
Am I forced to represent the input as text (using :'end') and then perform a conversion to integer?
Thanks!
David J.
On 3/16/2015 4:30 PM, David G. Johnston wrote:
> psql "$SERVICE" \
> --echo-queries \
> --set=string_input="${1:-ok_to_return}" \
> --set=start="${2:-5}" \
> --set=end="${3:-10}" \
> <<'SQL'
> SELECT idx
> FROM generate_series(1, 20) gs (idx)
> WHERE 'short-circuit' != :'string_input'
> AND idx BETWEEN :start AND :end;
> SQL
>
> # (6 rows)
>
> --set=end="${3:-10 AND false}"
>
> # (0 rows)
>
> Am I forced to represent the input as text (using :'end') and then
> perform a conversion to integer?
>
> Thanks!
>
> David J.
>
The --set's make it a little complicated. How about:
string_input="${1:-ok_to_return}"
start="${2:-5}"
end="${3:-10}"
psql "$SERVICE" --echo-queries <<'SQL'
prepare tmp as SELECT idx
FROM generate_series(1, 20) gs (idx)
WHERE 'short-circuit' != $1
AND idx BETWEEN $2 AND :$3;
execute tmp($string_input, $start, $end);
deallocate tmp;
SQL
That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
need to be passed to psql as-is, but $string_input, $start and $end need
to be replaced in bash before its sent to psql. Maybe use \$1?
Docs here:
http://www.postgresql.org/docs/9.4/static/sql-prepare.html
-Andy
On 3/16/2015 4:45 PM, Andy Colson wrote:
> On 3/16/2015 4:30 PM, David G. Johnston wrote:
>> psql "$SERVICE" \
>> --echo-queries \
>> --set=string_input="${1:-ok_to_return}" \
>> --set=start="${2:-5}" \
>> --set=end="${3:-10}" \
>> <<'SQL'
>> SELECT idx
>> FROM generate_series(1, 20) gs (idx)
>> WHERE 'short-circuit' != :'string_input'
>> AND idx BETWEEN :start AND :end;
>> SQL
>>
>> # (6 rows)
>>
>> --set=end="${3:-10 AND false}"
>>
>> # (0 rows)
>>
>> Am I forced to represent the input as text (using :'end') and then
>> perform a conversion to integer?
>>
>> Thanks!
>>
>> David J.
>>
>
>
> The --set's make it a little complicated. How about:
>
> string_input="${1:-ok_to_return}"
> start="${2:-5}"
> end="${3:-10}"
>
> psql "$SERVICE" --echo-queries <<'SQL'
> prepare tmp as SELECT idx
> FROM generate_series(1, 20) gs (idx)
> WHERE 'short-circuit' != $1
> AND idx BETWEEN $2 AND :$3;
>
> execute tmp($string_input, $start, $end);
> deallocate tmp;
> SQL
>
> That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
> need to be passed to psql as-is, but $string_input, $start and $end need
> to be replaced in bash before its sent to psql. Maybe use \$1?
>
> Docs here:
>
> http://www.postgresql.org/docs/9.4/static/sql-prepare.html
>
>
> -Andy
>
>
Wow. Sorry. what a mess.
> AND idx BETWEEN $2 AND :$3;
should be:
AND idx BETWEEN $2 AND $3;
> That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
should be: execute tmp($string_input, $start, $end);
-Andy
Re: How does one make the following psql statement sql-injection resilient?
From
"David G. Johnston"
Date:
Wow. Sorry. what a mess.On 3/16/2015 4:45 PM, Andy Colson wrote:On 3/16/2015 4:30 PM, David G. Johnston wrote:psql "$SERVICE" \
--echo-queries \
--set=string_input="${1:-ok_to_return}" \
--set=start="${2:-5}" \
--set=end="${3:-10}" \
<<'SQL'
SELECT idx
FROM generate_series(1, 20) gs (idx)
WHERE 'short-circuit' != :'string_input'
AND idx BETWEEN :start AND :end;
SQL
# (6 rows)
--set=end="${3:-10 AND false}"
# (0 rows)
Am I forced to represent the input as text (using :'end') and then
perform a conversion to integer?
Thanks!
David J.
The --set's make it a little complicated. How about:
string_input="${1:-ok_to_return}"
start="${2:-5}"
end="${3:-10}"
psql "$SERVICE" --echo-queries <<'SQL'
prepare tmp as SELECT idx
FROM generate_series(1, 20) gs (idx)
WHERE 'short-circuit' != $1
AND idx BETWEEN $2 AND :$3;
execute tmp($string_input, $start, $end);
deallocate tmp;
SQL
That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
need to be passed to psql as-is, but $string_input, $start and $end need
to be replaced in bash before its sent to psql. Maybe use \$1?
Docs here:
http://www.postgresql.org/docs/9.4/static/sql-prepare.html
-Andy
> AND idx BETWEEN $2 AND :$3;
should be:
AND idx BETWEEN $2 AND $3;
> That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
should be: execute tmp($string_input, $start, $end);
-Andy
Thanks! I got the gist even with the typo. I actually pondered about prepare/execute after hitting send. Am I correct in remembering that "CREATE TEMP TABLE" cannot be prepared? I was using the actual query with CREATE TEMP TABLE and then issuing "\copy" to dump the result out to the file. The limitation of copy to having to be written on a single line makes the intermediary temporary table seem almost a necessity.
I'd rather write the :'start'::integer than go through the prepare/execute cycle...
David J.
Re: How does one make the following psql statement sql-injection resilient?
From
Alvaro Herrera
Date:
David G. Johnston wrote: > Thanks! I got the gist even with the typo. I actually pondered about > prepare/execute after hitting send. Am I correct in remembering that > "CREATE TEMP TABLE" cannot be prepared? I was using the actual query with > CREATE TEMP TABLE and then issuing "\copy" to dump the result out to the > file. The limitation of copy to having to be written on a single line > makes the intermediary temporary table seem almost a necessity. CREATE TEMP TABLE AS EXECUTE -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: How does one make the following psql statement sql-injection resilient?
From
"David G. Johnston"
Date:
On Monday, March 16, 2015, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
David G. Johnston wrote:
> Thanks! I got the gist even with the typo. I actually pondered about
> prepare/execute after hitting send. Am I correct in remembering that
> "CREATE TEMP TABLE" cannot be prepared? I was using the actual query with
> CREATE TEMP TABLE and then issuing "\copy" to dump the result out to the
> file. The limitation of copy to having to be written on a single line
> makes the intermediary temporary table seem almost a necessity.
CREATE TEMP TABLE AS EXECUTE
Thanks.
Though unless I need to work on the temp table I think:
PREPARE ...;
\copy (EXECUTE ...) TO '~/temp.csv' ...;
Gives the best of all worlds.
David J.
Re: How does one make the following psql statement sql-injection resilient?
From
"David G. Johnston"
Date:
On Monday, March 16, 2015, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:David G. Johnston wrote:
> Thanks! I got the gist even with the typo. I actually pondered about
> prepare/execute after hitting send. Am I correct in remembering that
> "CREATE TEMP TABLE" cannot be prepared? I was using the actual query with
> CREATE TEMP TABLE and then issuing "\copy" to dump the result out to the
> file. The limitation of copy to having to be written on a single line
> makes the intermediary temporary table seem almost a necessity.
CREATE TEMP TABLE AS EXECUTEThanks.Though unless I need to work on the temp table I think:PREPARE ...;\copy (EXECUTE ...) TO '~/temp.csv' ...;Gives the best of all worlds.
Except that server "COPY" only is documented to accept a "query" that begins with either SELECT or VALUES :(
I hereby voice my desire for EXECUTE to be usable as well.
David J.
Re: How does one make the following psql statement sql-injection resilient?
From
Alvaro Herrera
Date:
David G. Johnston wrote: > Except that server "COPY" only is documented to accept a "query" that > begins with either SELECT or VALUES :( > > I hereby voice my desire for EXECUTE to be usable as well. Feel free to submit a patch ... -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: How does one make the following psql statement sql-injection resilient?
From
"David G. Johnston"
Date:
David G. Johnston wrote:
> Except that server "COPY" only is documented to accept a "query" that
> begins with either SELECT or VALUES :(
>
> I hereby voice my desire for EXECUTE to be usable as well.
Feel free to submit a patch ...
I get your point though if anyone else wants this before 2017 they shouldn't count on me.
David J.
Re: How does one make the following psql statement sql-injection resilient?
From
"David G. Johnston"
Date:
David G. Johnston wrote:
> Except that server "COPY" only is documented to accept a "query" that
> begins with either SELECT or VALUES :(
>
> I hereby voice my desire for EXECUTE to be usable as well.
Feel free to submit a patch ...I get your point though if anyone else wants this before 2017 they shouldn't count on me.
While I lack in C language skills I do possess wiki editing skills...ToDo item added.
David J.