From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How does one make the following psql statement sql-injection resilient? |
Date: | 2015-03-16 21:45:51 |
Message-ID: | 55074F0F.3030700@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2015-03-16 21:51:47 | Re: How does one make the following psql statement sql-injection resilient? |
Previous Message | David G. Johnston | 2015-03-16 21:30:45 | How does one make the following psql statement sql-injection resilient? |