Re: Sql injection attacks

Greg Stark <gsstark(at)mit(dot)edu> wrote:
>
> Bill Moran <wmoran(at)potentialtech(dot)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.

Personal preference, I guess, but I still don't like it. I feel like I've got
to push through 30 levels of abstraction just to get a query executed. I mean,
if I want to move some things around a bit to get a better query plan, I don't
want to have to figure out how the interface is going to juggle my arguments.

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

Yeah ... I don't know what I was thinking when I asked that question ...

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

Maybe, but it makes things more complicated the more dynamically the query
is generated. I'm talking about variable numbers of WHERE clauses and
dynamically chosen JOINs ans stuff ... where the SQL itself changes, as
well as the # of parameters.

For the most part, I agree with you on this point, although I wouldn't go
so far as to suggest that PQExec should be removed.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com