Thread: PQexec() with multiple sql statements.

Hello all,

I'm would like to know how PQexec() treats query passed as its parameter.
(PostgreSQL 7.0.3/Linux)

When I pass sql statements like

----------------- BEGIN --------------
CREATE TABLE tmp (
    id serial,
    txt text
);

INSERT INTO tmp (txt) VALUES ('test str');
----------------- END -----------------

INSERT INTO issues an error. "table tmp does not exist" (it is not an exact
message but basically it says so. It works if I pass statement  one by one.)

I've looked PostgreSQL Programmer's Manual, but I couldn't find why PQexec()
does this.
Could anyone explain why PQexec() does not allow INSERT after CREATE TABLE?

If PQexec() makes sure "multiple sql statements query", passed to it, is
executed atomically, I would like to take advantage of it.

Thanks in advance.

PS: I'm not using libpq directly, but from PHP. I believe pg_exec() in PHP is
just a wrapper function of PQexec(), isn't it?

--
Yasuo Ohgaki

At 09:07 AM 09-04-2001 +0900, Yasuo Ohgaki wrote:
>If PQexec() makes sure "multiple sql statements query", passed to it, is
>executed atomically, I would like to take advantage of it.

If you want stuff done reasonably atomically you use transactions. Which
implementation actually does the statements atomically if you do what you
suggest?

I don't see much to be gained. I see more to be lost in terms of security.

If you don't allow multiple SQL statements per query, it minimizes the
damage if someone screws up security on their website.

There are a LOT of websites out there which pass cgi parameters directly to
the database without any quoting or filtering.

If multiple SQL statements were supported, they'd probably be in a more
serious position: drop database, drop table, etc.

When I checked some time back some PHP + MySQL scripts allowed multiple SQL
statements per query - just stick a semicolon in somewhere, and you can do
ANY arbitrary SQL you want.

for example:

select * from mytable where field='$cgiparam'

Someone just has to make sure $cgiparam is
1';drop table blah; select '1

And then you're screwed. Of course you aren't supposed to write apps that
way, but anyone want to tell the masses out there?

Cheerio,
Link.

Yasuo Ohgaki wrote:
>
> Hello all,
>
> I'm would like to know how PQexec() treats query passed as its parameter.
> (PostgreSQL 7.0.3/Linux)
>

Queries in a multiple query are parsed and analyzed
at once before the execution. INSERT after CREATE
TABLE in a multiple query is impossible in 7.0.3.
Probably this is fixed in 7.1.

regards,
Hiroshi Inoue

Yasuo Ohgaki wrote:
>
> Hello all,
>
> I'm would like to know how PQexec() treats query passed as its parameter.
> (PostgreSQL 7.0.3/Linux)
>

Queries in a multiple query are parsed and analyzed
at once before the execution. INSERT after CREATE
TABLE in a multiple query is impossible in 7.0.3.
Probably this is fixed in 7.1.

regards,
Hiroshi Inoue

Thank for your reply.

"Lincoln Yeoh" <lyeoh@pop.jaring.my> wrote in message
news:3.0.5.32.20010409121916.00851100@192.228.128.13...

> When I checked some time back some PHP + MySQL scripts allowed multiple SQL
> statements per query - just stick a semicolon in somewhere, and you can do
> ANY arbitrary SQL you want.
>
> for example:
>
> select * from mytable where field='$cgiparam'
>
> Someone just has to make sure $cgiparam is
> 1';drop table blah; select '1
>
> And then you're screwed. Of course you aren't supposed to write apps that
> way, but anyone want to tell the masses out there?

Even with current pg_Exec (PQExec), the same thing can be done.
So any inputs from users must be checked anyway.

As most web programmers do not trust any user inputs, I check user inputs both
form class level (Using HTML form fields definition) and db class level (Using
metadata from system tables), so it should be fine. (There are many other
security protections at network/host/application/user levels also)

However, I see a some PHP scripts that do not handle user inputs properly. It's
worth to mention :)

I believe most PostgreSQL/PHP users do this, but anyway
Don't forget to use addslashes() for user inputs before feeding them to
PostgreSQL at least.

Regards,
--
Yasuo Ohgaki