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