another problem with stored procedures - Mailing list pgsql-general
| From | Ted Byers |
|---|---|
| Subject | another problem with stored procedures |
| Date | |
| Msg-id | 046b01c60cbf$50440640$6401a8c0@RnDworkstation Whole thread Raw |
| Responses |
Re: another problem with stored procedures
|
| List | pgsql-general |
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?
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/
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/
pgsql-general by date: