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