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

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