Skip site navigation (1) Skip section navigation (2)

Re: Question about PQexecParams

From: Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
To: Kenneth Marshall <ktm(at)rice(dot)edu>
Cc: Steve <steeeeeveee(at)gmx(dot)net>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Question about PQexecParams
Date: 2010-10-01 14:37:05
Message-ID: AANLkTikzr_RZZkqrq86TtL56Yez4jsoOC4DKHEEb440Y@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hey Kenneth,

There are three benefits:
>
> - reduces the CPU overhead in both the client and the DB server
>  for converting to/from ASCII numbers
>
You solution based on PL/pgSQL function. I am doubt that execution
of PL/pgSQL parser (loadable module, which aren't built into the
PostgreSQL server) would be cheaper than such conversion.
Furthermore, what if I want to SELECT from table which contains
fields of different types, including arrays? How proposed PL/pgSQL
function (which returns SETOF) can be using in this case?

- decreases the amount of network traffic by more than 1/2
>
Agree.


> - binary transmission of query parameters helps protect against
>  SQL injection attacks
>
PQexecParams() was invented to protect users from SQL injections
by separating parameters from SQL command and escaping then
as well. Furthermore, there is no way to execute more than one
command with PQexecParams() (according to the doc, this provides
an extra protection from SQL injections! :-)


>
> Obviously, this is not as important for low performance systems,
> with the possible exception of preventing SQL injection attacks,
> but on high performance systems, minimizing the CPU overhead
> due to data conversions is very useful indeed.
>
On the other hand, programming of such binary transmissions, IMO,
it very tedious and error prone that is not acceptable for highly reliable
systems.


>
> Whether or not someone chooses to use it would need to be
> evaluated on a case-by-case basis. I posted the code because
> the details on how to use binary array transmission needed
> close examination of the documentation as well as the database
> sources. If you are using PostgreSQL 8.3 or higher, and can
> use an add on library, libpqtypes provides a very nice and
> very useful API for managing binary parameter transmission.
> I really hope that it could be included in the core PostgreSQL
> to help others avoid the need to troll through the grotty
> inner-workings of the database to figure out how to do this
> using just libpq.
>
Thanks for sharing!


-- 
// Dmitriy.

In response to

pgsql-sql by date

Next:From: Ozer, PamDate: 2010-10-01 20:51:38
Subject: Random sort with distinct
Previous:From: Kenneth MarshallDate: 2010-09-30 19:14:08
Subject: Re: Question about PQexecParams

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group