#97 execute multiple selects as one statement

open-later
PgSQL (41)
3
2007-04-05
2007-04-04
longj
No

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)

Discussion

  • Billy G. Allie
    Billy G. Allie
    2007-04-05

    • priority: 5 --> 3
    • assigned_to: nobody --> ballie01
    • status: open --> open-later
     
  • Billy G. Allie
    Billy G. Allie
    2007-04-05

    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.

     
  • longj
    longj
    2007-04-05

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

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