Prepared statements with PostgresV2

I'm using PGConnection from the PostgresV2 package.

I'd like to use prepared statements to avoid SQL Injection and continuous recompilation of the query.

Ej:
conn execute: 'SELECT * FROM MYTABLE WHERE COLUMN = ?' withAll: (Array with: columnValue)


How can I do this?


Regards,

On 28/10/2013 1:59 PM, Esteban A. Maringolo wrote:
> I'm using PGConnection from the PostgresV2 package.
>
> conn execute: 'SELECT * FROM MYTABLE WHERE COLUMN = ?' withAll: (Array with:
> columnValue)
>
>
> How can I do this?

You cannot do it with the V2 protocol. I just looked over the postgres
docs at:
     http://www.postgresql.org/docs/7.4/static/protocol.html
     http://www.postgresql.org/docs/7.3/static/protocol.html

The feature you want is called "Extended Query" in the postgres docs,
and is appears in PostgreSQL 7.4, but not in PostgreSQL 7.3.

Note also that the V2 Frontend/Backend Protocol is used for PostgreSQL
7.3., whereas the V3 Protocol is used for PostgreSQL 7.4.

On 29/10/2013 4:56 AM, Esteban A. Maringolo wrote:
>
> I remember using prepared statements with PG back in PG 6.x with Perl's
> DBD::Pg, it was the recommended practice back then (circa '00).

Hmm, you're right. Prepared statements are independent of the
frontend/backend protocol that I had tunnel vision on.

Looking at the postgres docs, I see PREPARE and EXECUTE. I'll try to
start up a postgres server, and see if I can get it to work.

On 29/10/2013 9:32 PM, Yanni Chiu wrote:
>
> Looking at the postgres docs, I see PREPARE and EXECUTE. I'll try to
> start up a postgres server, and see if I can get it to work.

Here's an example:

TestPGConnection new executeAll: #(
'PREPARE test1 (text,text,integer,text) AS
     INSERT INTO films (code, title, did, kind)
     VALUES ($1,$2,$3,$4)'
'EXECUTE test1(''T_601'', ''Yojimbo'', 106, ''Drama'')'
).

TestPGConnection new executeAll: #(
'PREPARE test2 (text) AS SELECT * FROM films WHERE code LIKE $1'
'EXECUTE test2(''%0%'')'
).

TestPGConnection new executeAll: #(
'EXECUTE test2(''%1%'')'
).

But, the third example returned:
   ERROR:  prepared statement "test2" does not exist

So it seems to not remember the prepared statements across connection
cycles. Re-sending the prepared statement isn't good for performance,
but it does solve your security issue.