PHP calls a function like PostgreSQL?

Question

I have a function in PostgreSQL / plpgsql with the following signature:

CREATE OR REPLACE FUNCTION user_login(TEXT, TEXT) RETURNS SETOF _get_session AS $$ ... $$ 

Where _get_session is the view. The function works fine when called from phpPgAdmin, however, as I call it with PHP, I get the following error:

Warning: pg_query () [function.pg-query]: query failed: ERROR: type "session_ids" does not exist CONTEXT: compilation of the PL / pgSQL function "user_login" near line 2 in /home/sites/blah.com/index. php on line 69

The DECLARE section of the function contains the following variables:

 oldSessionId session_ids := $1; newSessionId session_ids := $2; 

The session_ids domain exists, and other functions that use the same domain work when called from the same script. PHP is as follows:

 $query = "SELECT * FROM $dbschema.user_login('$session_old'::TEXT, '$session'::TEXT)"; $result = pg_query($login, $query); 

I also tried this using ::session_ids instead of ::TEXT when calling the function, however I get the same error.

Help: o (

Answer 1

Just make your code simple:

 $query = "SELECT * FROM $dbschema.user_login($1, $2)"; $result = pg_query_params($login, $query, array($session_old, $session)); 

You are now safe from SQL injection.

But your function is still erroneous, there is no data type "session_ids". I think you want to use TEXT in the DECLARE part.

Answer 2

If your request spans multiple lines, then PHP most likely will not send them as part of the same transaction. If so, you have two options.

The first option is to send all requests to the same call

 pg_query("query1; query2; query3;"); 

The second option (and, in my opinion, the best) is to use transactions. This will allow you to make calls across multiple lines, although the begin statement will most likely be sent with the original request.

 pg_query("begin; query1;"); pg_query("query2;"); pg_query("commit;"); 

If an error occurs, replace the commit with a rollback and there will be no changes to db.

When working with Postgres, this is actually a good rule of thumb.