Re: Sql injection attacks - Mailing list pgsql-general

Bill Moran <wmoran@potentialtech.com> writes:

> I've hit a lot of problems with these type of interfaces making it very
> difficult to execute complex queries.  But it may just be my unfamiliarity
> with such coding conventions.  I avoid them because they're difficult, but
> they're difficult because I avoid them.

Occasionally I find I have to interpolate something like $join_type which I
either set to "OUTER" or "INNER" in my own code just prior to the query.
That's obviously safe since the data comes from static data in my code, not
network data. It also only results in two different plans, not any number of
plans depending on what data the user provides.

I also often have an idiom like:

 WHERE (?=0 OR data like '%'||?||'%')
   AND (?=0 OR size > ?)

And call it with parameters like

 $search ne '', $search,
 $min_size > 0, $min_size

This avoids having to construct different queries with different plans for all
the different combination of search constraints.

This matters even more when you start to worry about reducing planning time by
caching all your prepared statements. Perl DBI even provides a mechanism to do
this automatically. But to use it reliably you have to be sure you aren't
going to generate an infinite number of SQL statements with hard coded
parameters in them.


> However, how do you suggest that rule of thumb be done when working in C?

  http://www.postgresql.org/docs/7.4/interactive/libpq-exec.html#AEN20466

  The primary advantage of PQexecParams over PQexec is that parameter values
  may be separated from the command string, thus avoiding the need for tedious
  and error-prone quoting and escaping.

I would go so far as to suggest deprecating and eventually removing PQExec
entirely in favour of PQExecParams. There's no need for the former and having
people see the params parameter all the time might give them the idea that it
would be wise to use it.

Incidentally, you should be able to prepare queries and execute them later
like the DBI and PHP interfaces, but there's an odd comment in the docs:

  Presently, prepared statements for use with PQexecPrepared must be set up by
  executing an SQL PREPARE command, which is typically sent with PQexec
  (though any of libpq's query-submission functions may be used). A
  lower-level interface for preparing statements may be offered in a future
  release.

I don't think this is true any more. I think the low level protocol exists
now. It's possible the libpq method doesn't exist yet though.

--
greg