Re: How does one make the following psql statement sql-injection resilient?

On Mon, Mar 16, 2015 at 2:51 PM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> 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
>

​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.