postgresql - JDBC Postgres stored function call returning setof 'myType' -

i'm trying protect database sql injection. have many stored functions return setof predefined data types. example:

create type userloginuseridpasswordreturntype (   userid integer, -- user.id   password text  );  create or replace function "user_login_get_id_password"(usernamein text) returns setof userloginuseridpasswordreturntype $$ declare     sql text; begin       sql =  'select id, cryptpwd "user" username = ' || usernamein ||';';       return query execute sql; end; $$ language 'plpgsql'; 

i call these stored procedures java front-end. i've read, using callablestatement safer regards sql injection. have far:

public arraylist<string> userlogingetidpassword(string username){         arraylist<string> result = new arraylist<string>();         try{             string commandtext = "{call user_login_get_id_password(?)}";             this.cstmt = this.myconnection.preparecall(commandtext);             this.cstmt.setobject(1, username); //          this.rs = this.cstmt.execute();             this.rs = cstmt.executequery();             while (this.rs.next()){                 result.add(this.rs.getstring(1));             }         } catch (sqlexception e){             system.out.println("sql exception: ");             e.printstacktrace();         }         return result;     } 

if try using execute() method, asks me set resultset rs boolean. if run way (executequery()) first field (userid) of returntype can seen in resultset.

if call stored procedure such:

public arraylist<string> userlogingetidpassword(string username){         arraylist<string> result = new arraylist<string>();         try{             string query = "select \"user_login_get_id_password\"('" + username + "');";             system.out.println(query);             this.stmt = this.myconnection.createstatement();             this.rs = this.stmt.executequery(query);             while (this.rs.next()){                 result.add(this.rs.getstring(1));             }         } catch (sqlexception e){             system.out.println("sql exception: ");             e.printstacktrace();         }         return result;     } 

i correct data.

also, if there further tips protect database sql injection, kindly point them out. i've created specific postgres roles application use, implemented connectionpool (c3p0) connect database - application run on local network. i'm validating user input different java swing components in order avoid sql injection attacks (comment --, semi colon, * , other sql commands delete).

any input welcome.

thanks.

first, postgresql doesn't support call command that's why first approach fails.

on second approach have 2 problems. first sql injection. using standard binding apprach fix that. second problem difficult parse data function have called it.

a better approach like

select * user_login_get_id_password(?) 

however note sql injection possible inside function. extent can avoid execute protect you. if must use execute, change query string to:

sql =  'select id, cryptpwd "user" username = ' || quote_literal(usernamein); 

there no need terminating semicolon inside execute query. gets automatically terminated. need call quote_literal though (use quote_ident() if quoting table/column names).