[Pypgsql-devel] [ pypgsql-Bugs-1694437 ] execute multiple selects as one statement

[SourceForge.net <no...@so...>]

Bugs item #1694437, was opened at 2007-04-05 03:30
Message generated for change (Comment added) made by andrewmcnamara
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1694437&group_id=16528

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: PgSQL
Group: None
Status: Open
Resolution: Later
Priority: 3
Private: No
Submitted By: longj (longj)
Assigned to: Billy G. Allie (ballie01)
Summary: execute multiple selects as one statement

Initial Comment:
If your sql string contains multiple selects and using Cursor.execute to execute them, only the last statement was executed. But you can fool Cursor.execute by adding some comments at the beginning.

How to reproduce and get around:
----------In database---------
create table x (id serial);

create function f() returns void as $$
insert into x default values;
$$language sql;

---------In python-----------
from pyPgSQL import PgSQL
db = PgSQL.connect()
c = db.cursor()
sql1 = """select f();
select f();"""
sql2 = """--create table foo(bar int);
select f();
select f();"""
c.execute(sql1)
db.commit()

--------In database---------
select * from x;
 id 
----
  1
(1 row)

-------In python--------------
c.execute(sql2)
db.commit()

-------In database------------
select * from x;
 id 
----
  1
  2
  3
(3 rows)


----------------------------------------------------------------------

Comment By: Andrew McNamara (andrewmcnamara)
Date: 2007-04-30 17:56

Message:
Logged In: YES 
user_id=698599
Originator: NO

>Once I figure out a reliable RE to recognize multi-select queries, I
will
>put that in as a permenant fix.

I would rather see multiple queries per execute disallowed - it's a
classic vector for SQL injection attacks, and the newer libpc
PQexecParams() API doesn't allow it either.

----------------------------------------------------------------------

Comment By: longj (longj)
Date: 2007-04-06 04:41

Message:
Logged In: YES 
user_id=1761244
Originator: YES

Another thing I noticed is if you run
>>> cur.execute('select f()')
BEGIN WORK
DECLARE "PgSQL_081B414C" CURSOR FOR select f()
FETCH 1 FROM "PgSQL_081B414C"

It will run fetch one in this case. But won't in multi-select case. 
Seems this if statement was bypassed:
	elif _qstr[:8] == 'DECLARE ':
	    # Ok -- we've created a cursor, we will pre-fetch the first row in
	    # order to make the description array.  Note: the first call to
	    # fetchXXX will return the pre-fetched record.
	    self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
...

----------------------------------------------------------------------

Comment By: Billy G. Allie (ballie01)
Date: 2007-04-05 14:00

Message:
Logged In: YES 
user_id=8500
Originator: NO

PgSQL by default uses a PostgreSQL cursor for select statements (except
SELECT ... INTO and SELECT ... FOR UPDATE).  This causes the following SQL
to be generated and sent to the backend:

>>> cnx.conn.debug='y'
>>> cur.execute('select f(); select f(); select f();')
QUERY: BEGIN WORK
QUERY: DECLARE "PgSQL_-480D93F4" CURSOR FOR select f(); select f(); select
f();
>>> cnx.commit()
QUERY: CLOSE "PgSQL_-480D93F4"
QUERY: COMMIT WORK

This causes one of the select f(); statements to be lost.  This occurs in
psql also when given the sequence of queries shown above.

The work-around is to execute 'PgSQL.noPostgresCursor = True' before you
execute a multi-select query and to execute 'PgSQL.noPostgresCursor =
False' when you are done.  This disables the use of PostgreSQL cursors,
which will allow the statement to function as expected.

Once I figure out a reliable RE to recognize multi-select queries, I will
put that in as a permenant fix.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=1694437&group_id=16528