Thread: using postgresql functions from php

On Tue, Dec 07, 2004 at 02:35:58PM -0500, RobertD.Stewart@ky.gov wrote:

> I have created a function in postgresql

See comments below.

> CREATE OR REPLACE FUNCTION public.insert_vpn_masteraccount(varchar, varchar,
> varchar, varchar, varchar, varchar, varchar, varchar, varchar)
>   RETURNS varchar AS
> '
> DECLARE

You have an empty DECLARE section -- you can omit it if you don't
plan to use it.

> BEGIN
>  insert into masteraccount(fname,midint,lname,username,atype)
> values($1,$2,$3,$4,$5);
>
>  insert into passwd(u_id,currentpwd) values((select max(u_id) from
> masteraccount where username=$4),$6);

What's the purpose of the "select max(u_id)" subquery?  If
masteraccount.u_id is a SERIAL column then you could do this
instead:

  INSERT ... VALUES (currval(''masteraccount_u_id_seq''), $6);

Note the two single quotes, which are necessary if the function
body is defined in quotes.  PostgreSQL 8.0 will offer an alternate
way to quote strings so you won't have to do this.

>  insert into ipinfo(u_id,ipaddress,atype) values((select max(u_id) from
> masteraccount where username=$4),$7,$5);
>
>  insert into userinfo(u_id,agency,user_email) values((select max(u_id) from
> masteraccount where username=$4),$8,$9);

The same comment about using currval() applies to these inserts.

>  return masteraccount.u_id where masteraccount.username=$4;

Beware: the above line depends on the add_missing_from configuration
variable being turned on.  It would be safer to add the appropriate
FROM clause, but you could probably replace the entire statement
with:

  return currval(''masteraccount_u_id_seq'');

> END;
> '
>   LANGUAGE 'plpgsql' VOLATILE;
>
> I can insert data using this function with a sql statement  by typing
>
> select
> insert_vpn_masteraccount('tom','d','johnson','tomd.johnson','V','1234','DHCP
> ','AGR','idontcare@care.com'
>
> this works fine
>
> but when I try to do it from a php web page using
>
> $enter = $db_object->query("SELECT
> insert_vpn_masteraccount('$fname','$mi','$lname','$acc_type','$passwd','$ip'
> ,'$agency','$contact')");
>
> This does not work

Define "does not work" -- what happens?  One problem I see is that
the call in your PHP code passes only 8 arguments, whereas the
function expects 9.  The SQL statement that works passes 9 arguments.

I'll caution you about something else: SQL injection.  Have the
variables $fname, $mi, etc., been properly escaped to prevent
malicious code from being injected into your SELECT statement?
Malicious intent aside, a name like O'Reilly could cause problems
if the variables aren't escaped.  If your database interface supports
placeholders then I'd suggest using them (unless they're known to
be broken).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/