another problem with stored procedures

I have just encountered another problem. I am not sure if it is with my
code, or with how I am working with Postgres/pgAdmin III.

Here is another function, as created using the wizard/dialog box in pgAmin III for creating functions:

CREATE FUNCTION "People".get_pw(ea "varchar") RETURNS "varchar" AS
$BODY$
SELECT pword FROM "People".uids WHERE email_address = ea;
$BODY$
LANGUAGE 'sql' VOLATILE;

When I click <OK> to indicate that I am finished, I get an error message
saying there is no column called "ea". Of course I know that; that is
because it is a function parameter instead. What I don't understand is why
pgAdmin would not put the "IN" qualifier for the function's only parameter
or why Postgres would think ea is a column when the code clearly identifies
it as a function parameter. (BTW: replacing 'sql' by 'plpgsql' has no
effect, except the error message is even less informative).

Any ideas?

What I am after is a simple select procedure returning the contents of pword in the record where the contents of email_address are the same as the contents of the parameter ea. I figure that if the result set returned to the calling Java/JDBC code is empty, the email address offered does not exist in the database and that, if there is one record, I'll compare the string value returned with the password offered by the user in order to authenticate the user. Then, if authentication succeeds, I'll query a different database to see what resources the user is authorized to use.

I have used, through JDBC function calls that end up submitting something like the following to the RDBMS back end:

SELECT pword FROM "People".uids WHERE email_address = 'ea_value';

these all worked fine. It was just a little tedious to concatenate the various strings so that the contents of the SQL statement string looked like the above statement. I can't see a reason why I'd have trouble transforming the above select statement into a stored function.

BTW: I know I can do this my old way of using prepared statements with JDBC and java, but I read that I can make my distributed application more secure by putting all my SQL into stored, parameterized procedures. What are the SQL related attacks that a web application is vulnerable to, and how effective is the approach of placing all my SQL into stored procedures at countering them. Are prepared statements any more, or less, useful in making a distributed application more secure? Of course, I'd have validation code on both the client side and within my servlet that processes user data. After all, I have bitten the bullet to learn about stored procedures and functions precisely because of my studies of ways to make distributed applications secure.

Thanks,

Ted

R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/

On Thu, 29 Dec 2005, Ted Byers wrote:

> I have just encountered another problem. I am not sure if it is with my
> code, or with how I am working with Postgres/pgAdmin III.
>
> Here is another function, as created using the wizard/dialog box in pgAmin III for creating functions:
>
> CREATE FUNCTION "People".get_pw(ea "varchar") RETURNS "varchar" AS
> $BODY$
> SELECT pword FROM "People".uids WHERE email_address = ea;
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
> When I click <OK> to indicate that I am finished, I get an error message
> saying there is no column called "ea". Of course I know that; that is
> because it is a function parameter instead.

From the create function docs:
The name of an argument. Some languages (currently only PL/pgSQL) let
you use the name in the function body. For other languages the name of an
input argument is just extra documentation. But the name of an output
argument is significant, since it defines the column name in the result
row type. (If you omit the name for an output argument, the system will
choose a default column name.)

SQL language functions are definately in the "for other languages"
portion. I think you'll need to refer to it as $1 inside the function.

> What I don't understand is why
> pgAdmin would not put the "IN" qualifier for the function's only parameter
> or why Postgres would think ea is a column when the code clearly identifies
> it as a function parameter. (BTW: replacing 'sql' by 'plpgsql' has no
> effect, except the error message is even less informative).

Bare sql won't make a valid plpsql function, so you probably would get an
error at the select or some such.