|
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/ |
